RANK function in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to rank the value in cell A2 among the range A2:A10.

Excel

=RANK([1], A2:A10)

Drag options to blanks, or click blank then click option'

AB2

BA1

CA11

DA2

Attempts:

3 left

💡 Hint

Common Mistakes

Using a cell outside the range to rank.

Using a cell from a different column.

The RANK function needs the cell value to rank as the first argument. Here, it is A2.

2fill in blank

medium

Complete the formula to rank the value in B3 among the range B2:B10 in ascending order.

Excel

=RANK([1], B2:B10, 1)

Drag options to blanks, or click blank then click option'

AB2

BB3

CA3

DB10

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong cell reference for the value.

Confusing the range with the value cell.

The first argument should be the value you want to rank, which is in cell B3.

3fill in blank

hard

Fix the error in the formula to rank the value in C4 among C2:C10 in descending order.

Excel

=RANK(C4, [1], 0)

Drag options to blanks, or click blank then click option'

AC1:C9

BC4:C10

CC2:C10

DB2:B10

Attempts:

3 left

💡 Hint

Common Mistakes

Using a range that excludes some data.

Using a range from a different column.

The range to rank against must include all values, here C2:C10. Using C4:C10 excludes some values.

4fill in blank

hard

Fill both blanks to rank the value in D5 among D2:D10 in ascending order.

Excel

=RANK([1], [2], 1)

Drag options to blanks, or click blank then click option'

AD5

BD2:D10

CD3:D9

DD1:D10

Attempts:

3 left

💡 Hint

Common Mistakes

Using a partial range that misses some values.

Using the wrong cell for the value.

The first blank is the value to rank (D5). The second blank is the full range (D2:D10).

5fill in blank

hard

Fill all three blanks to rank the value in E6 among E2:E12 in descending order.

Excel

=RANK([1], [2], [3])

Drag options to blanks, or click blank then click option'

AE6

BE2:E12

C0

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 instead of 0 for descending order.

Using an incomplete range.

The first blank is the value cell (E6), the second is the range (E2:E12), and the third is 0 for descending order.

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