# Duplicates in RANK

#### jacc99

##### New Member
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Peter_SSs

##### MrExcel MVP, Moderator
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.

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.

#### DanteAmor

##### Well-known Member
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)),"")

Replies
1
Views
222
Replies
1
Views
554
Replies
1
Views
413
Replies
1
Views
124
Replies
55
Views
930

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,126
Messages
5,768,269
Members
425,460
Latest member
Astros1243

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

### Which adblocker are you using?

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

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