How to rank values in Excel with RANK, RANK.EQ, and RANK.AVG

Use RANK.EQ for shared ranks and RANK.AVG to average the positions occupied by tied values. Both rank the largest value first by default. Set the final argument to 1 when a lower value, such as a shorter race time, should rank first.

Choose a tie rule

Supply the number to rank and the range of numbers to compare. RANK.EQ assigns the best position in each tied group to every member. RANK.AVG assigns the mean of the positions the group occupies.

RANK is the older compatibility function and handles ties the same way as RANK.EQ. Use the newer function whose tie rule suits your worksheet.

Microsoft RANK · Microsoft RANK.EQ · Microsoft RANK.AVG

Try both functions on the same scores

Enter 84, 75, 75, and 63 in B2:B5 from top to bottom. Put the first formula below in C2 and the second in D2.

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

Fill C2:D2 down through row 5. The relative reference B2 changes with the row, while the dollar signs keep the comparison range fixed. Your regional settings may use a different argument separator.

RANK.EQ gives 1, 2, 2, and 4. RANK.AVG averages second and third place for the tie, giving 1, 2.5, 2.5, and 4. The score of 63 ranks fourth either way.

With three scores of 75, they occupy positions 2 through 4. RANK.EQ assigns all three rank 2 and RANK.AVG assigns rank 3. Use a separate rule if ranks must have no gaps or tied scores need a tiebreaker.

Rank smaller values first

Set the last argument to 1 if a smaller value should rank higher, such as a race time. Fill the formula down through the same scores.

=RANK.EQ(B2,$B$2:$B$5,1)

The result is 4, 2, 2, and 1, with 63 ranked first. Omit the last argument or use 0 to start with the largest value. A nonzero argument starts with the smallest.

Check unexpected rankings

Excel ignores nonnumeric values in the comparison range. Check that scores stored as text were converted to numbers, all intended rows are included, and the range stays fixed when copied.

Two cells can look the same when rounded for display while storing different values. If your rules rank the rounded scores, round them consistently in a helper column and use it for both the target value and the comparison range.