Cell Reference Math

[JR]

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.

 

[Bernard Liengme]

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%?

 

[JR]

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??

 

[Bernie Deitrick]

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

 

[JR]

Bernie

Thanks tons...that worked perfectly.....

 

[Bernie Deitrick]

Thanks tons...

You're quite welcome

that worked perfectly.....

But, of course ;-)

Bernie
MS Excel MVP