Duplicates in RANK

jacc99

New Member
Joined
Jun 14, 2021
Messages
12
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. MacOS
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!

example1.xlsm
ABCDEFGHIJKLMNOPQRSTUVWX
1ReturnsRANKWEIGHTS
20.004%0.007%-0.017%-0.006%-0.010%0.000%-0.001%0.003%21867453 1-1     
30.000%-0.022%-0.019%-0.001%-0.010%0.000%-0.001%0.000%187561411-1   1 1
Sheet1
Cell Formulas
RangeFormula
I2:P3I2=IFERROR(RANK(A2,$A2:$H2,0),"")
Q2:X3Q2=IFERROR(IFS(I2=SMALL($I2:$P2,1),1,I2=LARGE($I2:$P2,1),-1),"")
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Duplicates in RANK.
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
To respect the rules, please, You must provide the liks of the other forums.

_______________________________
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.

If the last period is the previous row, in this case, the current period is row 3, the previous period is row 2?
In the event of a tie in the first period (row 2), the formula breaks the tie considering the position from left to right.

Then try this:
Dante Amor
ABCDEFGHIJKLMNOP
1ReturnsRANK
20.004%0.000%-0.017%-0.006%-0.010%0.000%-0.001%0.003%13867452
30.000%-0.022%-0.019%-0.001%-0.010%0.000%-0.001%0.000%18756342
Hoja5
Cell Formulas
RangeFormula
I2:P3I2=IFERROR(RANK(A2,$A2:$H2,0)+IF($A1="Returns",COUNTIF($A$2:A2,A2)-1,COUNTIFS($A$3:$H$3,A2,$A$2:$H$2,">"&A1)),"")
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top