SOLVED

# evaluate series of numbers to rank in order

Copper Contributor

# evaluate series of numbers to rank in order

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 # 1 1 2 3 4 5 6 7 8 9 10 11 2 2 3 4 5 6 7 8 9 10 11 1 3 4 5 6 7 8 9 10 11 1 2 3 4 11 9 7 5 3 1 10 8 6 4 2 TOTAL 18 19 20 21 22 23 35 36 26 27 17 RANK 2 3 4 5 6 7 10 11 8 9 1
7 Replies
best response confirmed by rwhaynes954 (Copper Contributor)
Solution

# Re: evaluate series of numbers to rank in order

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

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

# Re: evaluate series of numbers to rank in order

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

in B2:

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

# Re: evaluate series of numbers to rank in order

Thank you! This worked perfectly.

# Re: evaluate series of numbers to rank in order

Thank you. In this particular case, I did not need a Dense Rank, but I now have that for future reference.

# Re: evaluate series of numbers to rank in order

You're welcome & Thank you for marking the solution

# Re: evaluate series of numbers to rank in order

As variant

``=RANK.EQ(B1:L1,B1:L1,1)``

# Re: evaluate series of numbers to rank in order

Sergei,
Thank you.
1 best response

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

# Re: evaluate series of numbers to rank in order

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

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