RANK function with exception

[cristian26]

Hello,

I have encountered a problem and I cannot find a solution to it. My Excel knowledge is good but it lacks certain areas.
I have a list of people and their scores. I need to order them in descending order. Everything is basic so far. My issue is as follows:

The requirement is that if any of the scores is "0", then the person/people scoring "0" must be ranked first. This is for a cards game similar to WHIST, in which the winner is determined either by the highest score or by the score of "0".

Could anyone please help me

 

[steve the fish]

This will need a bit a testing but it seems to work to rank numbers in B2:B10.

=SUMPRODUCT(--(IF(B2=0,9.99E+307,B2) < B$2:B$10))+IF(B2 < > 0,COUNTIF($B$2:$B$10,0))+1

 

[cristian26]

Thank you for replying. I do not understand what I should do with the formula.

 

[steve the fish]

Which cells have your people and your scores? Those cells are what the formula needs to be applied to.

 

[cnestg8r]

=if(b2<>0,1+rank(b2,$b$2:$b$10,0),1)

 

[steve the fish]

That wouldn't work with ties of 0, assuming that's possible of course.