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