Ranks and Index and Match

[Nevsy]

Good Morning everyone

i have a data set that takes stock holdings from a portfolio and lists it alphabetically and the holding percentage (that is how the data comes in). in a column i have used the rank function to determine what rank within the portfolio each holding is. now, as bad luck would have it, the percentage holdings come to 2 decimal places and therefore it often happens that two holdings are the same size and so the rank function returns the same rank.

in another part of the spread sheet i have a list showing the top 10 holdings and to do this i use the index and match function to pull hold the holdings ranked 1 to 10.

however, lets say there are 2 holdings both ranked 9: therefore, there is no holding ranked 10 so i get an error and i am unable to design a formula to find the second holding that is ranked at 9.

any ideas?

thanks

Nevsy

 

[Richard Schollar]

Hi

Assume the percentage is in col B and your rank is in col C (cells B2:B10 for the sake of argument). In col C use this formula to rank:

=RANK(B2,$B$2:$B$10)+COUNTIF($B$2:$B2,$B2)-1

This will give you a list without duplicates based on the percentage and then based on the actual position within the list for duplicates (ie those appearing first rank higher).

 

[Nevsy]

that was bang on the money, thank you for that Richard

oh, and weldone on gaining MVP status last October

 

[Richard Schollar]

You're welcome

Thank you!