How to assign scores in order based on resultsI have

Question

How to assign scores in order based on resultsI have

Brian Atkinson 20

I have 10 results. I would like to assign scores in numerical order based on: 9 for highest, 8 for 2nd highest, 7 for third highest ...down to zero for lowest. Example scores are: A 7 B 4 C 5 D 5 E 5 F 9 G 3 H 3 I 1 J 2.

The resuld should be: F 9, A 7, D 5, E 5, F 5, B 4, G 3, H 3, I 1, J 2. I can't find a way to do this.

EmilyS726 264.4K Reputation points Independent Advisor

2026-09-06T21:04:00.1566667+00:00

Brian, can you share a screenshot of what the raw data looks like?

For example, are the letters all in one column, where the numbers are in another column, or? I need to see how the raw data is being presented in order to come up with a solution that meets your desired result. Thanks!
Zahid Barrera Ramirez 5 Reputation points

2026-09-06T21:14:35.1066667+00:00

If your values are in B2:B11, you can use RANK.EQ:

=RANK.EQ(B2,$B$2:$B$11,0)

This ranks the highest value as 1, the second highest as 2, etc., while giving equal values the same rank.

If you specifically want the highest result to return 9 and the lowest to return 0, use:

=10-RANK.EQ(B2,$B$2:$B$11,0)

One small correction to your example: I think the second "F 5" should be "C 5".

If you want tied values such as C, D and E to receive consecutive scores instead of the same score, that requires a slightly different formula because we need to define how ties should be broken.
Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-09-06T22:55:22.3733333+00:00

Hi,

Cannot understand your question. Please explain in detail. Also, are all those inputs in a single cell or in a column.
Brian Atkinson 20 Reputation points

2026-09-06T23:03:09.3566667+00:00

Screen Shot

Answer accepted by question author

IlirU 2,651 Volunteer Moderator

User's image

Hi @Brian Atkinson

Try this formula in cell E3:

=SORT(B3:C12, {2,1}, {-1,1})

Hope this helps.

IlirU

0 comments

3 additional answers

Your answer

EmilyS726 264.4K Reputation points Independent Advisor

2026-09-06T21:04:00.1566667+00:00

Brian, can you share a screenshot of what the raw data looks like?

For example, are the letters all in one column, where the numbers are in another column, or? I need to see how the raw data is being presented in order to come up with a solution that meets your desired result. Thanks!
Zahid Barrera Ramirez 5 Reputation points

2026-09-06T21:14:35.1066667+00:00

If your values are in B2:B11, you can use RANK.EQ:

=RANK.EQ(B2,$B$2:$B$11,0)

This ranks the highest value as 1, the second highest as 2, etc., while giving equal values the same rank.

If you specifically want the highest result to return 9 and the lowest to return 0, use:

=10-RANK.EQ(B2,$B$2:$B$11,0)

One small correction to your example: I think the second "F 5" should be "C 5".

If you want tied values such as C, D and E to receive consecutive scores instead of the same score, that requires a slightly different formula because we need to define how ties should be broken.
Ashish Mathur 102.4K Reputation points Volunteer Moderator

2026-09-06T22:55:22.3733333+00:00

Hi,

Cannot understand your question. Please explain in detail. Also, are all those inputs in a single cell or in a column.
Brian Atkinson 20 Reputation points

2026-09-06T23:03:09.3566667+00:00

Screen Shot

Answer 1

Brian Atkinson 20

Screenshot

User's image

EmilyS726 264.4K Reputation points Independent Advisor

2026-09-07T00:01:46.04+00:00

Thanks for the screenshot. Just to clarify what you are trying to achieve:

Do you want Excel to simply sort the existing scores from highest to lowest, or do you want Excel to assign new ranking scores of 9 for 1st place, 8 for 2nd, 7 for 3rd, and so on down to 0?

Also, how should ties be handled? For example, C, D, and E all have a score of 5. Should they receive the same rank/score, or different consecutive scores?
Barry Schwarz 6,106 Reputation points

2026-09-07T05:33:38.0066667+00:00

Please explain why I1 is Higher than J2.

Can you also confirm the titles in your sample data are reversed.

Answer 2

riny 21,520 Volunteer Moderator

@Brian Atkinson Provided you're on a modern Excel version, this might work for you.

User's image

0 comments

Answer 3

Brian Atkinson 20

I was having trouble making Rank.eq work. I had never seen this formula before. Then I realized I had mistakes in my example. THen I discivered RANK.AVG which is what I wanted. Thanks! Brian

0 comments