RANK function in Excel - Cell-by-Cell Formula Trace

Sample Data

This table shows test scores in column A. We want to find the rank of each score in column B using the RANK function.

Cell	Value
A1	Scores
A2	85
A3	92
A4	75
A5	92
A6	68
B1	Rank
B2
B3
B4
B5
B6

Formula Trace

=RANK(A2, $A$2:$A$6, 0)

Step 1: RANK(85, {85, 92, 75, 92, 68}, 0)

Cell Reference Map

    A     |    B    
---------------------
1 | Scores |  Rank   
2 |  85    |  -> formula here
3 |  92    |         
4 |  75    |         
5 |  92    |         
6 |  68    |         

Arrows: B2 formula uses A2 and range A2:A6

The formula in B2 uses the value in A2 and the range A2:A6 to calculate the rank.

Result

    A     |    B    
---------------------
1 | Scores |  Rank   
2 |  85    |    4    
3 |  92    |         
4 |  75    |         
5 |  92    |         
6 |  68    |         

Cell B2 shows the rank of score 85 as 4.

The rank of 85 among the scores is 4, so cell B2 shows 4.

Sheet Trace Quiz - 3 Questions

What does the RANK function compare to find the rank of 85?

A It compares 85 only to A2

B It compares 85 to scores in B2:B6

C It compares 85 to all scores in A2:A6

D It compares 85 to the average of scores

Key Result

RANK(value, range, order) returns the position of value in range sorted by order (0=descending, 1=ascending).