RANK function in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

What is the output of the RANK function?

Given the values in cells A1 to A5 as 10, 20, 20, 40, 30 respectively, what is the result of the formula =RANK(A3, A1:A5)?

A 4

B 3

C 1

D 2

💡 Hint

Remember that RANK returns the rank of a number in a list, with the highest number ranked 1 by default.

The value in A3 is 20. The list is 10, 20, 20, 40, 30. The highest value is 40 (rank 1), then 30 (rank 2), then both 20s (rank 3), then 10 (rank 5). Since RANK assigns the same rank to duplicates, both 20s get rank 3. So the rank of A3 (20) is 3.

❓ Function Choice

intermediate

Which formula ranks numbers in ascending order?

You want to rank the number in cell B2 among the range B2:B10, where the smallest number gets rank 1. Which formula should you use?

A =RANK(B2, B2:B10, 1)

B =RANK(B2, B2:B10)

C =RANK(B2, B2:B10, 0)

D =RANK.AVG(B2, B2:B10)

💡 Hint

Check the third argument of RANK to control ascending or descending order.

The RANK function's third argument controls order: 0 or omitted means descending (largest is rank 1), 1 means ascending (smallest is rank 1). So =RANK(B2, B2:B10, 1) ranks ascending.

📊 Formula Result

advanced

What is the output of RANK with ties and descending order?

Given the values in C1:C6 as 50, 40, 40, 30, 20, 10, what is the result of =RANK(C3, C1:C6, 0)?

A 2

B 3

C 1

D 4

💡 Hint

Remember that RANK assigns the same rank to duplicate values.

C3 is 40. The list descending is 50 (rank 1), both 40s (rank 2), 30 (rank 4), 20 (rank 5), 10 (rank 6). Both 40s get rank 2. So the rank of C3 is 2.

🎯 Scenario

advanced

Ranking with dynamic range and ignoring blanks

You have scores in cells D1:D10, but some cells are blank. You want to rank the score in D5 among only the non-blank scores, with the highest score ranked 1. Which formula correctly does this?

A =RANK(D5, IF(D1:D10<>"", D1:D10))

B =RANK(D5, D1:D10, 1)

C =RANK(D5, D1:D10)

D =RANK(D5, FILTER(D1:D10, D1:D10<>""))

💡 Hint

RANK automatically ignores blank cells and non-numeric values.

Option C uses RANK directly, which ignores blanks (non-numeric), ranking D5 only among non-blank scores (highest rank 1 by default). Option C ranks ascending. Option C is invalid syntax (IF returns FALSE for blanks). Option C uses FILTER (Excel 365+ only).

❓ data_analysis

expert

How many unique ranks are assigned?

Given the values in E1:E8 as 100, 90, 90, 80, 70, 70, 70, 60, if you apply =RANK(E1, E1:E8) down the column, how many unique rank numbers will appear?

A 8

B 6

C 4

D 5

💡 Hint

Count how many distinct values are in the list since duplicates share ranks.

The distinct values are 100, 90, 80, 70, 60 — 5 unique values. RANK assigns the same rank to duplicates, so only 5 unique ranks appear.