 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2005Cell Reference Math
I am using Exel to analyze mechanical test date. The data files can be 600 to 6000 points. I need to capture an 50 point average at the 25% and 75% points in the data. Is there a way to auto adjust where the averages are taken based upon the number of data points. I can get the number of data points using the COUNTIF function. Is there a way to add this result as the numeric part of a cell reference and do the appropriate math to select the correct ranges. I hope this explanation makes sense. Could you please explain: <I need to capture an 50 point average at the 25% and 75% points in the data.> You want an average of the top 25% and the lower 75%?  SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email >I am using Exel to analyze mechanical test date. The data files can be > 600 to 6000 points. I need to capture an 50 point average at the 25% [quoted text clipped - 4 lines] > appropriate math to select the correct ranges. I hope this explanation > makes sense. When the data set is 600 points long, I need a 50 point average at 150-200 and 450-500. When the data set is 6000 points long, I need a 50 point average at 1500-1550 and 4500-4550. Does this exaplin it?? JR, Please don't multi-post the same question. My answer from worksheet functions.... How about these, for data in column A, and a title value in cell A1 =AVERAGE(OFFSET(A1,COUNT(A:A)*0.25-25,0,50,1)) and =AVERAGE(OFFSET(A1,COUNT(A:A)*0.75-25,0,50,1)) HTH, Bernie MS Excel MVP >I am using Exel to analyze mechanical test date. The data files can be > 600 to 6000 points. I need to capture an 50 point average at the 25% [quoted text clipped - 4 lines] > appropriate math to select the correct ranges. I hope this explanation > makes sense. Bernie Thanks tons...that worked perfectly..... > Thanks tons... You're quite welcome >that worked perfectly..... But, of course ;-) Bernie MS Excel MVP |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.