Average within quartiles

  • Thread starter Thread starter rk0909
  • Start date Start date
R

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
 
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)
 
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)
 
thanks guys. You are always a great help.

Jim Thomlinson said:
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)
Click to expand...
 
Back
Top