calculating a mean from a percentage of cells in a column

[mcphie13]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Intel

Hi,

How would I write a formula to get the average of the first 25% of a column of numbers, the second 50% and then the last 25%? I then want to copy and paste these formulas get the same information from columns of different lengths of cells.

 

[JE McGimpsey]

Version: 2004
Operating System: Mac OS X 10.4 (Tiger)
Processor: Intel

Hi,

How would I write a formula to get the average of the first 25% of a column
of numbers, the second 50% and then the last 25%? I then want to copy and
paste these formulas get the same information from columns of different
lengths of cells.

If by "first 25%", you mean the the first 25% by position (e.g., if
there were 52 entries, A1:A52, you'd take the average of A1:A13, the
middle 50% would average A14:A39, and the last 25% would average
A40:A52), one way:

Assume number in column A, starting in A1

First 25%:

=AVERAGE(OFFSET($A$1,0,0,ROUND(COUNT(A:A)/4,0),1))

Next 50%:

=AVERAGE(OFFSET($A$1,ROUND(COUNT(A:A)/4,0),0,COUNT(A:A)-
2*ROUND(COUNT(A:A)/4,0),1))

Last 25%:

=AVERAGE(OFFSET($A$1,COUNT(A:A)-1,0,-ROUND(COUNT(A:A)/4,0),1))