Average within quartiles
 

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

Answer: Average within quartiles
 

Yes, there is an easy way to calculate the average of data by quartile in Excel. Here are the steps:

1. Select the range of data that you want to analyze.
2. Go to the "Data" tab in the ribbon and click on "Sort".
3. In the "Sort" dialog box, select the column that you want to sort by and choose "Smallest to Largest" or "Largest to Smallest" depending on your preference.
4. Click "OK" to sort the data.
5. In a new column, use the
   Formula:

   ---

   "QUARTILE.INC" 


   ---

   function to calculate the quartile for each row. For example, if your data is in column A and you want to calculate the quartile in column B, you would use the formula
   Formula:

   ---

   "=QUARTILE.INC(A1:A100,1)" 


   ---

   for the first quartile,
   Formula:

   ---

   "=QUARTILE.INC(A1:A100,2)" 


   ---

   for the second quartile, and so on.
6. Once you have calculated the quartile for each row, you can use the
   Formula:

   ---

   "AVERAGEIF" 


   ---

   function to calculate the average for each quartile. For example, if your data is in column A, the quartiles are in column B, and you want to calculate the average for the first quartile in column C, you would use the formula
   Formula:

   ---

   "=AVERAGEIF(B1:B100,1,A1:A100)" 


   ---

   .

To answer your second question, yes, you can calculate similar averages for a second column based on the quartiles defined in the first range. You would use the same steps as above, but replace the range in the "AVERAGEIF" function with the range of the second column that you want to analyze.

Average within quartiles
 

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)

--
HTH...

Jim Thomlinson


"rk0909" wrote:

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

Average within quartiles
 

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)

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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)

--
HTH...

Jim Thomlinson


"rk0909" wrote:

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

Average within quartiles
 

thanks guys. You are always a great help.

"Jim Thomlinson" wrote:

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)

--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

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)

--
HTH...

Jim Thomlinson


"rk0909" wrote:

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