Search code examples
excelexcel-formulauniquerank

A counter example for the Unique ranking formula of Excel - Any Idea how to resolve it?

We all know that famous formula for ranking uniquely in excel when numbers are repeated which is:

=RANK(X5,$X$1:$X$10,0)+COUNTIF($X$1:X5,X5)-1

For instance the above formula shows the rank of the 5th number in a column "X" within 10 numbers.

I was using this and happened to find a counter example where this does not work for:

enter image description here

As you can see we have rank 6 twice. Any thoughts?

I tried it again and strangely this time it gives 5 for both B2 and B17 (following image). I have set a workaround formula in the modified column but do not know whether this will be true for all cases.

enter image description here

Solution

  • I get 5 in B17 when reproducing, not 6.

    Therefore reproducing gives the expected behavior, not what you see.

    Check the following:

    1. Are B17 and B2 exactly the same? (you may need to widen the column and/or set the format to General to determine the actual values for these cells, not just the displayed values).
    2. Do you have "precision as displayed" set in Options?
    3. Do you have any settings that encourage rounding results to round up?

    If no issue there, try providing your actual precise cell values here (if they differ to what is displayed in the screenshot).

    If still stuck, I could dig out some other versions to check. What software version did you see this result with?

    Here is my result:

    enter image description here