Average within quartiles

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
 
J

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)
 
J

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)
 
R

rk0909

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top