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:"&ROUND UP(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(F V14/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


--
jdeumer
------------------------------------------------------------------------
jdeumer's Profile: http://www.excelforum.com/member.php...o&userid=25181
View this thread: http://www.excelforum.com/showthread...hreadid=386710