Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting into quintiles
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 |
#2
|
|||
|
|||
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)),--(RangeCEILING(PERC ENTILE(Range,G3),1)),Range)/SUMPRODUCT(--(Range<=CEILING(PERCENTILE(Range,G4 ),1)),--(RangeCEILING(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 "jdeumer" wrote in 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:"&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(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...o&userid=25181 | View this thread: http://www.excelforum.com/showthread...hreadid=386710 | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
Sorting 1, 1A, 2, 2A, 3, 4, 4A, 4B . . . | Excel Discussion (Misc queries) | |||
sorting number in ascending order | Excel Discussion (Misc queries) | |||
Adding a KeyID column for sorting | New Users to Excel | |||
sorting question | Excel Discussion (Misc queries) |