cell reference math

[JR]

I have a spreadsheet that is filled by experimental data. The number of data
points (rows) can be 300-6000. I am tasked to take 50 point averages at the
25% and 75% points of the data. I can get the number of data points using
using the COUNTIF function. I need to be able to adjust the AVERAGE function
based on the COUNTIF results. Is there a way to set the cell reference based
upon this result? Are there any other ideas as to how to solve this? If my
problem is not clear, please ask questions.

[Bernie Deitrick]

JR,

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" <[email protected]> wrote in message
news:[email protected]...
>I have a spreadsheet that is filled by experimental data. The number of data
> points (rows) can be 300-6000. I am tasked to take 50 point averages at the
> 25% and 75% points of the data. I can get the number of data points using
> using the COUNTIF function. I need to be able to adjust the AVERAGE function
> based on the COUNTIF results. Is there a way to set the cell reference based
> upon this result? Are there any other ideas as to how to solve this? If my
> problem is not clear, please ask questions.