Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

evaluate series of numbers to rank in order

Copper Contributor

In Excel 2021, I want to evaluate the numbers in "TOTAL" row (cells B508-L508) and then in the "RANK" row (cells B510-L510) rank the results with a "1" in the "RANK" row under the lowest number in the "TOTAL" row.

Here is an example to illustrate what I am trying to do. Currently, I have to enter the ranking number in the "RANK" row manually.

 

Survey #           
11234567891011
22345678910111
34567891011123
41197531108642
            
TOTAL1819202122233536262717
            
RANK2345671011891
7 Replies
best response confirmed by rwhaynes954 (Copper Contributor)
Solution

Hi @rwhaynes954 

 

Sample.png

 

in B2 - making sure C2:L2 cells are empty:

=XMATCH(B1:L1, SORT(B1:L1,,,TRUE))

@rwhaynes954 

 

In case you have duplicate and you want a Dense Rank:

Sample.png

 

in B2:

 

=XMATCH(B1:L1, SORT(UNIQUE(B1:L1,TRUE),,,TRUE))

 

Thank you! This worked perfectly.
Thank you. In this particular case, I did not need a Dense Rank, but I now have that for future reference.
You're welcome & Thank you for marking the solution

@rwhaynes954 

As variant

=RANK.EQ(B1:L1,B1:L1,1)
Sergei,
Thank you.
1 best response

Accepted Solutions
best response confirmed by rwhaynes954 (Copper Contributor)
Solution

Hi @rwhaynes954 

 

Sample.png

 

in B2 - making sure C2:L2 cells are empty:

=XMATCH(B1:L1, SORT(B1:L1,,,TRUE))

View solution in original post