Sorting into quintiles

[jdeumer]

L.S.,

For my thesis I need to sort stocks into quintiles and calculate the
average per quintile. I am using the "LARGE" function to do this.
However, the number of available stocks differs per month, so that the
value for "k" (in this case a range) needs to be adapted every time.
Furthermore, the value for k needs to be rounded up for the first four
quintiles, the last quintile consists of the remaining number of
stocks.

Currently I am using this formula to calculate the average of stocks
that need to be sorted into quintiles, for the first quintile:

{=AVERAGE(LARGE($B14:$FT14,ROW(INDIRECT("1:"&ROUNDUP(FV14/5,0)))))}

where
$B14:$FT14 = the range with all available stocks and
Cell FV14 contains the total number of stocks available for this
specific month.
ROW(INDIRECT("1:"&ROUNDUP(FV14/5,0))) indicated the range for 'k'.

This seems to work (I checked). For the second quintile however I want
the formula to do this: start from ROUNDUP(FV14/5,0) + 1 until 2 *
ROUNDUP(FV14/5,0). Intuitively, this would look like this, but does not
work:

{=AVERAGE(LARGE($B14:$FT14,ROW(INDIRECT("ROUNDUP(FV14/5+1,0):"&(2*ROUNDUP(FV14/5,0))))))}


I find it hard to fully understand the INDIRECT function, which might
explain my difficulty creating this formula.

Thanks for your help!

RJE Deumer

 

[ScottO]

I think a found a way ...
If your data is in the range called "Range" and you enter into Cells G3 to
G7 the ascending values 0.2 to 1.0 (formatted to % if you want)
Then in H3 put the Array formula
{=AVERAGE(IF(Range<=CEILING(PERCENTILE(Range,G3),1),Range))} (do CSE)

Then in H4 put the regular formula
=SUMPRODUCT(--(Range<=CEILING(PERCENTILE(Range,G4),1)),--(Range>CEILING(PERC
ENTILE(Range,G3),1)),Range)/SUMPRODUCT(--(Range<=CEILING(PERCENTILE(Range,G4
),1)),--(Range>CEILING(PERCENTILE(Range,G3),1)),Range/Range)

Then drag the H4 formula down to H7.

Unless I've missed something, that should do it.

Rgds,
ScottO


message |
| L.S.,
|
| For my thesis I need to sort stocks into quintiles and calculate the
| average per quintile. I am using the "LARGE" function to do this.
| However, the number of available stocks differs per month, so that the
| value for "k" (in this case a range) needs to be adapted every time.
| Furthermore, the value for k needs to be rounded up for the first four
| quintiles, the last quintile consists of the remaining number of
| stocks.
|
| Currently I am using this formula to calculate the average of stocks
| that need to be sorted into quintiles, for the first quintile:
|
| {=AVERAGE(LARGE($B14:$FT14,ROW(INDIRECT("1:"&ROUNDUP(FV14/5,0)))))}
|
| where
| $B14:$FT14 = the range with all available stocks and
| Cell FV14 contains the total number of stocks available for this
| specific month.
| ROW(INDIRECT("1:"&ROUNDUP(FV14/5,0))) indicated the range for 'k'.
|
| This seems to work (I checked). For the second quintile however I want
| the formula to do this: start from ROUNDUP(FV14/5,0) + 1 until 2 *
| ROUNDUP(FV14/5,0). Intuitively, this would look like this, but does not
| work:
|
|
{=AVERAGE(LARGE($B14:$FT14,ROW(INDIRECT("ROUNDUP(FV14/5+1,0):"&(2*ROUNDUP(FV
14/5,0))))))}
|
|
| I find it hard to fully understand the INDIRECT function, which might
| explain my difficulty creating this formula.
|
| Thanks for your help!
|
| RJE Deumer
|
|
| --
| jdeumer
| ------------------------------------------------------------------------
| jdeumer's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=25181
| View this thread: http://www.excelforum.com/showthread.php?threadid=386710
|