RANK - Non unique values, contiguous rank required cannot be found

Try this formula in row 2 copied down

=IF(LEFT(A2,6)="Leader","",SUMPRODUCT(((LEFT(A$2:A$27,6)<>"leader")*(B$2:B$27<B2))/COUNTIF(B$2:B$27,B$2:B$27&""))+1)

Wow, it is pretty cool, not exaclty what i was looking for but solves the problem and stops skipping numbers. Thanks daddy.

One issue is the "real" sheet doesnt have leader in that place but a name with a z at the beggining, for example:

Name
John
Robert
Peter
Carl
Karen
Claire
Johnson
Joe
zMark

This to visualize who is the leader for that team.

Looks like this cool work if used =IF(LEFT(A2,6)="z","",SUMPRODUCT((((LEFT(A$2:A$27,6)<>"z")*(B$2:B$27<B2))/COUNTIF(B$2:B$27,B$2:B$27&""))+1))

What do you think? best way to attack that? What if the person has a "z" in the name? could it affect the results?


Attached in sheet 2 you can see a more detailed version

Does this do what you want. It is daddylonglegs' formula with "Ω" instead of the "Z"? The Omega is just a character that is very unlikely to be found. The Omega can be found in the Symbols. Insert it into your worksheet then copy it and paste into your formula if this is something useful.

Formula:

Please Login or Register  to view this content.

because "Leader" has 6 characters I had to use LEFT(A2,6), for "z" that should be a 1, i.e.

=IF(LEFT(A2,1)="z","",SUMPRODUCT(((LEFT(A$2:A$27,1)<>"z")*(B$2:B$27<B2))/COUNTIF(B$2:B$27,B$2:B$27&""))+1)

That only discounts any name beginning with "z" (so you can't have "Zach"!)

or this version will consider names beginning with upper case "Z" but not lower case "z"

=IF(EXACT(LEFT(A2),"z"),"",SUMPRODUCT(((EXACT(LEFT(A$2:A$27),"z")+0=0)*(B$2:B$27<B2))/COUNTIF(B$2:B$27,B$2:B$27&""))+1)

I think the parentheses are wrong on the version you are using - you need to use the formula I suggested in my last post so that ranks begin at 1, i.e.

Originally Posted by daddylonglegs

=IF(LEFT(A2,1)="z","",SUMPRODUCT(((LEFT(A$2:A$27,1)<>"z")*(B$2:B$27<B2))/COUNTIF(B$2:B$27,B$2:B$27&""))+1)