Average within quartiles

[rk0909]

I have thousands of rows of data (not sorted).
is there a (easy) way to calculate the average of this data by quartile.
that is average of the bottom 25% of this data, average of next 25% of data
and so on so forth.

Also an added question if we can solve the above is that can i do similar
averages of data on a second column but based on rankings (or quartiles
defined) in the first range?

thanks and looking forward to expert advice as always.

RK

 

[Jim Thomlinson]

Here is one way that does not use volatile functions. With data in A1:A30

1st
=SUMPRODUCT(($A$1:$A$30<QUARTILE($A$1:$A$30, 1)) * $A$1:$A$30)

2nd
=SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 1)) *
($A$1:$A$30<QUARTILE($A$1:$A$30, 2))* $A$1:$A$30)

3rd
=SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 2)) *
($A$1:$A$30<QUARTILE($A$1:$A$30, 3))* $A$1:$A$30)

4th
=SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 3))* $A$1:$A$30)

 

[Jim Thomlinson]

Sorry that was sum and not average.

=SUMPRODUCT(($A$1:$A$30<QUARTILE($A$1:$A$30, 1)) *
$A$1:$A$30)/SUMPRODUCT(($A$1:$A$30<QUARTILE($A$1:$A$30, 1)) * 1)

=SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 1)) *
($A$1:$A$30<QUARTILE($A$1:$A$30, 2))*
$A$1:$A$30)/SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 1)) *
($A$1:$A$30<QUARTILE($A$1:$A$30, 2))* 1)

=SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 2)) *
($A$1:$A$30<QUARTILE($A$1:$A$30, 3))*
$A$1:$A$30)/SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 2)) *
($A$1:$A$30<QUARTILE($A$1:$A$30, 3))* 1)

=SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 3))*
$A$1:$A$30)/SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 3))* 1)

 

[rk0909]

thanks guys. You are always a great help.

Sorry that was sum and not average.

=SUMPRODUCT(($A$1:$A$30<QUARTILE($A$1:$A$30, 1)) *
$A$1:$A$30)/SUMPRODUCT(($A$1:$A$30<QUARTILE($A$1:$A$30, 1)) * 1)

=SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 1)) *
($A$1:$A$30<QUARTILE($A$1:$A$30, 2))*
$A$1:$A$30)/SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 1)) *
($A$1:$A$30<QUARTILE($A$1:$A$30, 2))* 1)

=SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 2)) *
($A$1:$A$30<QUARTILE($A$1:$A$30, 3))*
$A$1:$A$30)/SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 2)) *
($A$1:$A$30<QUARTILE($A$1:$A$30, 3))* 1)

=SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 3))*
$A$1:$A$30)/SUMPRODUCT(($A$1:$A$30>=QUARTILE($A$1:$A$30, 3))* 1)