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 2, 3 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 weight of 1 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, I3, N3, P3 have the same rank in the second period, and thus they have been assigned the same weight of 1 in Q, V, X. I need it to assign the weight of 1 to I only out of those three, as its rank last period was the lowest at rank 1.
Really appreciate any help, Many thanks in advance!
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 2, 3 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 weight of 1 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, I3, N3, P3 have the same rank in the second period, and thus they have been assigned the same weight of 1 in Q, V, X. I need it to assign the weight of 1 to I only out of those three, as its rank last period was the lowest at rank 1.
Really appreciate any help, Many thanks in advance!
| example1.xlsm | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |||
| 1 | Returns | RANK | WEIGHTS | |||||||||||||||||||||||
| 2 | 0.004% | 0.007% | -0.017% | -0.006% | -0.010% | 0.000% | -0.001% | 0.003% | 2 | 1 | 8 | 6 | 7 | 4 | 5 | 3 | 1 | -1 | ||||||||
| 3 | 0.000% | -0.022% | -0.019% | -0.001% | -0.010% | 0.000% | -0.001% | 0.000% | 1 | 8 | 7 | 5 | 6 | 1 | 4 | 1 | 1 | -1 | 1 | 1 | ||||||
Sheet1 | ||||||||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| I2:P3 | I2 | =IFERROR(RANK(A2,$A2:$H2,0),"") |
| Q2:X3 | Q2 | =IFERROR(IFS(I2=SMALL($I2:$P2,1),1,I2=LARGE($I2:$P2,1),-1),"") |
