Hi there,
So i have a bunch of stock price returns that i am ranking relative to one another. I am then trying to assign weights based on the highest 2 and lowest 2. These should sum to net 0. However, i occasionally have two stocks with 0% returns. This causes an issue whereby RANK obviously ranks these equally, however this causes the net to be -0.5, -1 or else etc.
I am wondering if there is any way i can formulate the function whereby if two or more cells have the same return and thus ranked the same, it will select the stock with the lowest rank last period out of said stocks. i.e. if stock A B C have rank 2 2 2 respectively this period, but 1 3 5 last period, it will assign the 0.5 weight to stock A as it had the lowest RANK last period (1).
I have added a cut out so you can see where my issue lies. As can be seen, AM, AN, AO have the same rank in the second period, and thus they have been assigned the same weight of 0.5 in AU, AV, AW. I need it to assign the 0.5 weight to AM only out of those three, as its rank last period was the lowest at rank 1.
Many thanks
So i have a bunch of stock price returns that i am ranking relative to one another. I am then trying to assign weights based on the highest 2 and lowest 2. These should sum to net 0. However, i occasionally have two stocks with 0% returns. This causes an issue whereby RANK obviously ranks these equally, however this causes the net to be -0.5, -1 or else etc.
I am wondering if there is any way i can formulate the function whereby if two or more cells have the same return and thus ranked the same, it will select the stock with the lowest rank last period out of said stocks. i.e. if stock A B C have rank 2 2 2 respectively this period, but 1 3 5 last period, it will assign the 0.5 weight to stock A as it had the lowest RANK last period (1).
I have added a cut out so you can see where my issue lies. As can be seen, AM, AN, AO have the same rank in the second period, and thus they have been assigned the same weight of 0.5 in AU, AV, AW. I need it to assign the 0.5 weight to AM only out of those three, as its rank last period was the lowest at rank 1.
Many thanks
| 2019 Lookback returns data.xlsm | ||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| AH | AI | AJ | AK | AL | AM | AN | AO | AP | AQ | AR | AS | AT | AU | AV | AW | |||
| 139589 | Rank | Weights | ||||||||||||||||
| 139590 | 6 | 7 | 8 | 3 | 5 | 1 | 4 | 2 | -0.5 | -0.5 | 0.5 | 0.5 | ||||||
| 139591 | 5 | 7 | 8 | 6 | 1 | 2 | 2 | 2 | -0.5 | -0.5 | 0.5 | 0.5 | 0.5 | 0.5 | ||||
Cross Sectional Ranks | ||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| AH139590:AO139591 | AH139590 | =IFERROR(RANK('Lookback Data'!AH139590,'Lookback Data'!$AH139590:$AO139590,0),"") |
| AP139590:AW139591 | AP139590 | =IFERROR(IFS(AH139590=SMALL($AH139590:$AO139590,2),0.5,AH139590=SMALL($AH139590:$AO139590,1),0.5,AH139590=LARGE($AH139590:$AO139590,2),-0.5,AH139590=LARGE($AH139590:$AO139590,1),-0.5),"") |
Last edited:
