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.

Practice

1. What does the Excel function LARGE(range, k) do?

easy

A. Returns the smallest number in the range

B. Returns the k-th largest number in the range

C. Returns the sum of the largest k numbers

D. Returns the average of the range

Solution

1. Step 1: Understand the function purpose

   The LARGE function is designed to find the k-th largest value in a list or range of numbers.

2. Step 2: Compare options with function behavior

   Only Returns the k-th largest number in the range correctly describes that it returns the k-th largest number, not the smallest, sum, or average.

3. Final Answer:

   Returns the k-th largest number in the range -> Option B

4. Quick Check:

   LARGE(range, k) = k-th largest number [OK]

Hint: LARGE picks biggest; SMALL picks smallest number [OK]

Common Mistakes:

• Confusing LARGE with SUM or AVERAGE
• Thinking LARGE returns smallest values
• Mixing up k-th largest with total count

2. Which of these is the correct syntax to find the 3rd smallest number in cells A1 to A10?

easy

A. =SMALL(A1:A10, 3)

B. =SMALL(3, A1:A10)

C. =SMALL(A1:A10)

D. =SMALL(A1:A10, "3")

Solution

1. Step 1: Recall SMALL function syntax

   The correct syntax is SMALL(range, k) where range is the data and k is the position.

2. Step 2: Check each option

   =SMALL(A1:A10, 3) matches the correct syntax. =SMALL(3, A1:A10) reverses arguments, =SMALL(A1:A10) misses k, =SMALL(A1:A10, "3") uses a string instead of a number.

3. Final Answer:

   =SMALL(A1:A10, 3) -> Option A

4. Quick Check:

   SMALL(range, k) correct syntax [OK]

Hint: Use SMALL(range, k) with number k, not string [OK]

Common Mistakes:

• Swapping arguments order
• Omitting the k value
• Using quotes around k

3. Given the values in cells A1 to A5: 10, 25, 15, 30, 20, what is the result of =LARGE(A1:A5, 2)?

medium

A. 30

B. 20

C. 15

D. 25

Solution

1. Step 1: List values and sort descending

   The values are 10, 25, 15, 30, 20. Sorted largest to smallest: 30, 25, 20, 15, 10.

2. Step 2: Identify 2nd largest value

   The 2nd largest value is 25.

3. Final Answer:

   25 -> Option D

4. Quick Check:

   LARGE(..., 2) = 2nd largest = 25 [OK]

Hint: Sort values mentally to find k-th largest [OK]

Common Mistakes:

• Choosing the largest instead of 2nd largest
• Confusing LARGE with SMALL
• Miscounting the position k

4. You wrote =SMALL(A1:A5, 0) to find the smallest number. What is wrong and how to fix it?

medium

A. k cannot be zero; change 0 to 1

B. Range is wrong; use A1:A6 instead

C. Use LARGE instead of SMALL

D. Add quotes around 0 like "0"

Solution

1. Step 1: Understand k parameter rules

   In SMALL(range, k), k must be a positive integer starting at 1 to pick the smallest, 2nd smallest, etc.

2. Step 2: Identify the error and fix

   Using 0 for k is invalid. Change it to 1 to get the smallest value.

3. Final Answer:

   k cannot be zero; change 0 to 1 -> Option A

4. Quick Check:

   k ≥ 1 for SMALL function [OK]

Hint: k must be 1 or more, never zero [OK]

Common Mistakes:

• Using zero or negative k values
• Changing range unnecessarily
• Confusing SMALL with LARGE

5. You have sales data in B2:B11. To find the 3rd highest sale excluding zeros, which formula works correctly?

hard

A. =LARGE(B2:B11, 3)

B. =SMALL(B2:B11, 3)

C. =LARGE(IF(B2:B11>0, B2:B11), 3) (entered as array formula)

D. =LARGE(B2:B11, 4)

Solution

1. Step 1: Understand the problem requirement

   We want the 3rd largest sale but only counting values greater than zero (exclude zeros).

2. Step 2: Analyze each option

   =LARGE(B2:B11, 3) includes zeros, so might pick zero if it is among top 3. =LARGE(IF(B2:B11>0, B2:B11), 3) (entered as array formula) uses IF to filter out zeros and requires array formula entry, which is correct. =SMALL(B2:B11, 3) uses SMALL, wrong direction. =LARGE(B2:B11, 4) picks 4th largest, not 3rd.

3. Final Answer:

   =LARGE(IF(B2:B11>0, B2:B11), 3) -> Option C

4. Quick Check:

   Filter zeros with IF, then LARGE(..., 3) [OK]

Hint: Use IF to exclude zeros inside LARGE with array formula [OK]

Common Mistakes:

• Not excluding zeros, getting wrong rank
• Using SMALL instead of LARGE
• Wrong k value for position