excelexcel-formulaexcel-2013

RANK formula gives incorrect result when values exist more than one

1) Copy the following values and paste into A1:C8 cells in excel sheet.

Header of A column Header of B column Header of C column
600 4 =RANK(A2,$A$2:$A$8,0)
1200 2 =RANK(A3,$A$2:$A$8,0)
500 5 =RANK(A4,$A$2:$A$8,0)
900 3 =RANK(A5,$A$2:$A$8,0)
500 5 =RANK(A6,$A$2:$A$8,0)
1300 1 =RANK(A7,$A$2:$A$8,0)
300 6 =RANK(A8,$A$2:$A$8,0)

2) The following picture shows that Rank formula gives wrong result

This picture shows that RANK formula gives wrong result.

3) RANK formula gives wrong result because 500 exists in A column two times.

4) Do you know alternative formula against RANK formula?

5) Update: Desired solution is found thanks to Mayukh Bhattacharya like following.

SUMPRODUCT((A2<=$A$2:$A$8)/COUNTIF($A$2:$A$8;$A$2:$A$8))
Solution

  • The formula which can help is:

    [B2]=LET(src,A2:A8,u,UNIQUE(src),XLOOKUP(src,SORT(u,,-1),SEQUENCE(ROWS(u))))

    enter image description here

    UNIQUE leaves only unique sales to allow the same sales occupy a single place.

    SORT sorts unique sales descending.

    For every sales (src), XLOOKUP finds it in the sorted unique sales to get the corresponding place from the sequence of places (1 to 6).

    LET supports to define temporary variables like 'src' and 'u' for repetitive use.

    Shorter formulas!

    [B2]=LET(src,A2:A8,XMATCH(src,SORT(UNIQUE(src),,-1)))

    In this formula, XMATCH provides the place number without SEQUENCE generation.

    The same without LET:

    =XMATCH(A2:A8,SORT(UNIQUE(A2:A8),,-1))

    One more formula which counts the number of unique values qreater then the left value is for each cell of B2:B8:

    =SUM(--(UNIQUE($A$2:$A$8)>=A2))

    Enter this formula into B2 and fill down up to B8.

    Credits to @MayukhBhattacharya who has found the formula for earlier Excel versions. With a small modification:

    [B2]=SUM((A2<=$A$2:$A$8)/COUNTIF($A$2:$A$8;$A$2:$A$8))

    Enter this formula into B2 and fill down up to B8.