RANK function in Excel - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the RANK function do in Excel?

The RANK function tells you the position of a number in a list compared to other numbers. For example, it shows if a number is the highest, second highest, and so on.

beginner

Write the basic syntax of the RANK function.

RANK(number, ref, [order])

Where:
- number is the value you want to rank.
- ref is the range of numbers to compare.
- order is optional: 0 or omitted means descending (highest rank is 1), 1 means ascending (lowest rank is 1).

intermediate

What happens if two numbers are the same in the RANK function?

If two numbers are the same, they get the same rank. The next rank number skips accordingly. For example, if two numbers tie for 2nd place, both get rank 2, and the next rank will be 4.

beginner

How do you rank numbers in ascending order using the RANK function?

Use the RANK function with the order argument set to 1. For example:
RANK(A2, A$2:A$10, 1)
This ranks numbers so that the smallest number gets rank 1.

beginner

Give a real-life example where the RANK function can be useful.

You can use RANK to find the top scores in a test. For example, if you have student scores in a list, RANK can tell you who scored 1st, 2nd, 3rd, etc., easily.

What does the RANK function return when two numbers are the same?

A The same rank for both numbers

B An error message

C Different ranks for each number

D The average of their ranks

The RANK function assigns the same rank to numbers that are equal.

What is the default order used by the RANK function if the order argument is omitted?

A Ascending order (smallest number is rank 1)

B Random order

C Descending order (largest number is rank 1)

D Alphabetical order

By default, RANK ranks numbers in descending order, so the largest number gets rank 1.

Which formula ranks the number in cell A2 among the range A2:A10 in ascending order?

A RANK(A2, A2:A10, 1)

B RANK(A2, A2:A10, 0)

C RANK(A2, A2:A10)

D RANK(A2, A2:A10, -1)

Using 1 as the third argument ranks numbers in ascending order.

If two numbers tie for 3rd place, what rank will the next number get?

A 6

B 3

C 4

D 5

If two numbers tie for 3rd, both get rank 3, and the next rank skips to 5.

Which of these is NOT a valid use of the RANK function?

A Finding the highest sales number rank

B Calculating the sum of numbers

C Ranking numbers in ascending order

D Ranking students by test scores

RANK does not calculate sums; it only ranks numbers.

Explain how the RANK function works and how to use it to rank numbers in descending and ascending order.

Think about how you find the highest or lowest number's position in a list.

Describe what happens when there are ties in the numbers you rank using the RANK function.

Consider how two students with the same score share the same position.

Practice

1. What does the Excel RANK function do?

easy

A. It adds all numbers in a list.

B. It finds the position of a number in a list.

C. It counts how many numbers are in a list.

D. It finds the average of numbers in a list.

Solution

1. Step 1: Understand the purpose of RANK

   The RANK function returns the position of a number compared to others in a list.

2. Step 2: Compare with other options

   Options A, C, and D describe sum, count, and average, which are different functions.

3. Final Answer:

   It finds the position of a number in a list. -> Option B

4. Quick Check:

   RANK = position in list [OK]

Hint: RANK shows where a number stands in a list [OK]

Common Mistakes:

• Confusing RANK with SUM or COUNT functions
• Thinking RANK calculates averages
• Assuming RANK sorts the list

2. Which of these is the correct syntax for the RANK function in Excel?

easy

A. =RANK(number, ref, order)

B. =RANK(ref, number, order)

C. =RANK(number, order, ref)

D. =RANK(order, number, ref)

Solution

1. Step 1: Recall RANK function syntax

   The correct syntax is =RANK(number, ref, order), where 'number' is the value to rank, 'ref' is the list, and 'order' is optional.

2. Step 2: Check each option

   Only =RANK(number, ref, order) matches the correct order of arguments.

3. Final Answer:

   =RANK(number, ref, order) -> Option A

4. Quick Check:

   Syntax = RANK(number, ref, order) [OK]

Hint: Remember: number first, then list, then order [OK]

Common Mistakes:

• Swapping number and list arguments
• Placing order argument before list
• Forgetting order is optional

3. Given the list {50, 80, 80, 30, 90}, what is the result of =RANK(80, A1:A5, 0) if these values are in cells A1 to A5?

medium

A. 2

B. 3

C. 1

D. 4

Solution

1. Step 1: Understand RANK with order 0 (descending)

   Order 0 ranks highest number as 1. The list sorted descending is 90 (1), 80 (2), 80 (2), 50 (4), 30 (5).

2. Step 2: Find rank of 80

   Both 80s share rank 2 because they tie. So RANK(80) = 2.

3. Final Answer:

   2 -> Option A

4. Quick Check:

   80 is second highest, so rank = 2 [OK]

Hint: Order 0 means highest number gets rank 1 [OK]

Common Mistakes:

• Assuming ranks increase by 1 after ties
• Confusing ascending and descending order
• Ignoring ties and giving different ranks

4. You used the formula =RANK(A2, A1:A5, 1) but got unexpected ranks. What is the likely error?

medium

A. RANK function does not accept the order argument.

B. The order argument should be 0 for ascending rank.

C. The range A1:A5 includes the cell A2 itself.

D. The order argument 1 means ascending rank, but you expected descending.

Solution

1. Step 1: Understand order argument meaning

   Order 1 means ascending rank (lowest number is rank 1). If you expected highest number as rank 1, order should be 0.

2. Step 2: Identify mismatch in expectation

   The unexpected ranks happen because order 1 ranks lowest first, opposite of what was expected.

3. Final Answer:

   The order argument 1 means ascending rank, but you expected descending. -> Option D

4. Quick Check:

   Order 1 = ascending, check expectation [OK]

Hint: Order 1 ranks lowest first; use 0 for highest first [OK]

Common Mistakes:

• Confusing order 0 and 1 meanings
• Thinking order argument is optional but needed
• Including the cell itself in the range causes no error

5. You have scores in cells A1:A6: 85, 92, 85, 70, 92, 60. You want to rank the score in A3 using =RANK(A3, A1:A6, 0). What rank will it show and why?

hard

A. 1, because 85 is the highest score

B. 2, because 85 is the second highest unique score

C. 3, because 92 is rank 1, 85 is rank 3 with ties sharing rank

D. 4, because ties increase rank numbers

Solution

1. Step 1: Sort scores descending and assign ranks

   Scores sorted: 92 (rank 1), 92 (rank 1), 85 (rank 3), 85 (rank 3), 70 (rank 5), 60 (rank 6).

2. Step 2: Find rank of score in A3 (85)

   Since 85 appears twice, both get rank 3. Ties share the same rank, and ranks after skip numbers.

3. Final Answer:

   3, because 92 is rank 1, 85 is rank 3 with ties sharing rank -> Option C

4. Quick Check:

   Ties share rank, next rank skips numbers [OK]

Hint: Ties share rank; next rank skips numbers [OK]

Common Mistakes:

• Assuming ranks increase by 1 after ties
• Ignoring ties and giving different ranks
• Confusing rank order with ascending instead of descending