Rank Formula Help

[klatlap]

Hi, i am using this formula to rank a list of ratings and it works good because it avoids equal rankings but it ranks from lowest to highest how can i reverse that so it ranks highest to lowest.

=IF(C9="","",IF(N9="","",COUNT($N$9:$N$33)-(RANK(N9,$N$9:$N$33)+COUNTIF($N$9:N33,N33)-1)+1))

 

[repairman615]

Hello,

Try adding a 3rd argument to the RANK(ref,range,1)

Like:


=RANK(B1,B1:B6,1)


Not sure if this will cause the tie breaker to need reversing...


-Jeff

 

[klatlap]

It sort of works but dual ranked 2 ratings that were different to each other.

 

[repairman615]

I have a feeling that is to do with the "tie breaking" portion of the formula.

 

[klatlap]

I could add another column to rank the results but wanted to add it to this formula if possible.

 

[repairman615]

I do think it is possible.

Looking at it it is hard to tell without any data such as what you are using.

Would you post what is in the range("N9:N33")

also maybe the OR() could help simplify a bit:

=IF(OR(C9="",N9=""),"",COUNT($N$9:$N......

 

[klatlap]

Never mind, i just added another column, thanks for the help any ways.

 

[repairman615]

I am curious of the two formulas that worked.

Would you please post those?

-Jeff

 

[klatlap]

I used my original formula.

=IF(C9="","",IF(O9="","",COUNT($O$9:$O$33)-(RANK(O9,$O$9:$O$33)+COUNTIF($O$9:O10,O10)-1)+1))

This ranked lowest to highest, then in the the next column i put.

=IF(M9="","",RANK(M9,$M$9:$M$33))
This ranks highest to lowest and basically reverses the rank order so the top rated becomes the No 1 selection and so on,

 

[klatlap]

I think that first formula was incorrect as it didn't work, here is correct formula.

=IF(C9="","",IF(O9="","",COUNT($O$9:$O$33)-(RANK(O9,$O$9:$O$33)+COUNTIF($O$9:O9,O9)-1)+1))