Duplicate Data ranking issue

Ok so if I have a list of say 500 companies and I'm calculating their year over year return and I then want to rank them from highest to lowest. I'm using the Large( formula to rank them in order from 1-500. The problem I run into is there may be companies with the exact same yoy return number (rare but it happens that the numbers are exactly the same). Is there a formula I can use that will somehow fix this so I don't have two of the same companies in my ranked list table? Any help would be much appreciated.

Cheers

You should be able to manage this, but it's difficult to say exactly how without seeing a sample of your data. I find that the easiest way is usually to build a tiny decimal into your values that is then hidden by formatting, so that you have a build in tie-breaker. In my attachment, I've but the companies in column A and the returns in column B, then in F2 I use the following formula, array-entered with Ctrl + Shift + Enter instead of Enter:

=LARGE($B$2:$B$8+0.0001*ROW($B$2:$B$8),ROW(1:1))

Then in G2 (non-array):

=INDEX($A$2:$A$8,10000*(F2-INT(F2))-1)

Then fill down as far as you need to. Format the cells in column F to show only the digits you want showing, and you should have a ranking in which ties are no longer problematic. Take a look at the attachment; hopefully it proves helpful:

I've attached an example. On sheet #1 is the formula I was using to rank them in order and on sheet #2 are the returns and companies in no real order.

I posted an example because I have the data on two different sheets and tried to use the formula you posted in your example but can't seem to get it to work. Thanks for your help!

I think I may have found a solution I just want to check with you to see if you agree that this would work. So in the formula I have to calculate the return this is what I've done and it seems to work ok. (a5/c5-1)+Row()/1000000000

In the case of a tie, what's your desired result? In your sample post, Cells C30 and C31 both show 2.0% - do you want 2.0% to only appear once, then continue with the list so that C31 becomes 1.4%? Should the 2.0% appear in both cells but not actually be perfectly equivalent? What's the overall goal?

I would need both numbers and if they are exactly the same like the 2% and 2% I don't really care which one shows up on top of the other. I use those return numbers once ranked to then look up off of to find and pull in the ticker symbol of the company associated with that return which is where I run into trouble. That's why I was hoping to find a way to cause the return calcs to be ever so slightly different, hence the reason I tried using the return formula then adding +row()/1000000000 on to the end.

I've attached a new example which might help clarify where I 'm having the issue. I highlighted the duplicate ticker symbols that I get with the lookup formula I'm using. Hopefully this helps explain the situation better

Thanks!

Thank you for the clarification - give the new attachment a look. I use the following (array-entered) in C4:

=LARGE(Sheet2!$C$2:$C$51+0.0000001*ROW(Sheet2!$C$2:$C$51),Sheet1!A4)

And in D4 (non-array):

=INDEX(Sheet2!$B$2:$B$51,10000000*(C4-LARGE(Sheet2!$C$2:$C$51,Sheet1!A4))-1)

Works Great!! Thanks so much