CODEWITHSUNDEEP
excelrankingrankconsecutive-months
topstuff
topstuff

Reputation: 129

Excel - Ranking consecutively with duplicate values

Please see image below the "Numbers" column are the values I want to rank.
The "Preferred Ranking" is what I want as the results except I am having real trouble trying to get there!
Do you have any idea how I can achieve this? Thanks in advance.

enter image description here

Upvotes: 2

Views: 124

Answers (3)

Harun24hr
Harun24hr

Reputation: 37050

Simpler way is using XMATCH() with SORT() and UNIQUE().

=XMATCH(A1,SORT(UNIQUE($A$1:$A$20)))

Upvotes: 2

user11222393
user11222393

Reputation: 5501

=SUM(1*(A1>=UNIQUE($A$1:$A$17))) and drag it down or =MAP(A1:A17,LAMBDA(z,SUM(1*(z>=UNIQUE(A1:A17))))) to spill whole result at once.

Result:

enter image description here

Upvotes: 2

Scott Craner
Scott Craner

Reputation: 152605

Use UNIQUE to return a unique list.

This will spill down the range:

=LET(
    RNG,A2:A22,
    BYROW(RNG,LAMBDA(Z,COUNT(UNIQUE(FILTER(RNG,RNG<Z)))+1))
    )

enter image description here

Upvotes: 2

Related Questions