LARGE and SMALL in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Find the 3rd largest value in a list

Given the numbers in cells A1 to A6: 5, 8, 2, 10, 7, 3, what is the result of the formula =LARGE(A1:A6, 3)?

Excel

=LARGE(A1:A6, 3)

A 7

B 8

C 5

D 10

💡 Hint

Remember, LARGE returns the nth largest value. Here n=3.

The sorted list from largest to smallest is 10, 8, 7, 5, 3, 2. The 3rd largest is 7.

📊 Formula Result

intermediate

Find the 2nd smallest value in a list

Given the numbers in cells B1 to B5: 12, 4, 9, 15, 7, what is the result of the formula =SMALL(B1:B5, 2)?

Excel

=SMALL(B1:B5, 2)

A 9

B 7

C 4

D 12

💡 Hint

SMALL returns the nth smallest value. Here n=2.

The sorted list from smallest to largest is 4, 7, 9, 12, 15. The 2nd smallest is 7.

❓ Function Choice

advanced

Choose the correct formula to find the 4th largest value

You have a list of numbers in cells C1 to C10. Which formula correctly returns the 4th largest number?

A =LARGE(C1:C10, 4)

B =SMALL(C1:C10, 4)

C =LARGE(C1:C10, 7)

D =SMALL(C1:C10, 7)

💡 Hint

LARGE returns the nth largest, SMALL returns nth smallest.

To get the 4th largest, use LARGE with 4 as the second argument.

❓ data_analysis

advanced

Analyze the output of combined LARGE and SMALL formulas

Given the values in D1:D8: 3, 6, 1, 9, 4, 7, 2, 8, what is the result of =LARGE(D1:D8, 2) - SMALL(D1:D8, 3)?

Excel

=LARGE(D1:D8, 2) - SMALL(D1:D8, 3)

A 3

B 6

C 5

D 4

💡 Hint

Find the 2nd largest and 3rd smallest values first, then subtract.

Sorted descending: 9,8,7,6,4,3,2,1. 2nd largest is 8. Sorted ascending: 1,2,3,4,6,7,8,9. 3rd smallest is 3. 8 - 3 = 5.

🎯 Scenario

expert

Identify the error in this formula using LARGE and SMALL

You want to find the 5th largest value in a range E1:E4 containing {10, 20, 30, 40}. Which option describes what happens when you use =LARGE(E1:E4, 5)?

Excel

=LARGE(E1:E4, 5)

A Returns 10 because it treats 5 as the smallest value

B Returns 0 because it cannot find the 5th largest

C Returns 40 because it defaults to the largest value

D Returns #NUM! error because 5 is greater than the number of values

💡 Hint

Check what happens if the k argument is larger than the number of values.

LARGE returns a #NUM! error if k is greater than the number of values in the range.