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%?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"JR" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>

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


"JR" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.
>

Bernie

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

> Thanks tons...

You're quite welcome

>that worked perfectly.....

But, of course ;-)

Bernie
MS Excel MVP