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

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
Joined
May 28, 2005
Messages
52,302
Office Version
  1. 365
Platform
  1. Windows
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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,183
Office Version
  1. 2007
Platform
  1. Windows
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)),"")
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top