LARGE and SMALL in Excel - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the formula to find the 3rd largest number in the range A1:A10.

Excel

=LARGE(A1:A10, [1])

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

A1

B10

C5

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Using 1 instead of 3 returns the largest number, not the 3rd largest.

Using 10 might cause an error if there are fewer than 10 numbers.

The formula =LARGE(A1:A10, 3) returns the 3rd largest number in the range A1:A10.

2fill in blank

medium

Complete the formula to find the smallest value in the range B2:B20.

Excel

=SMALL(B2:B20, [1])

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

A1

B0

C2

D20

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 is invalid because the position must be 1 or greater.

Using 20 returns the 20th smallest, which might not exist.

The formula =SMALL(B2:B20, 1) returns the smallest value in the range B2:B20.

3fill in blank

hard

Fix the error in the formula to find the 2nd smallest number in C1:C15.

Excel

=SMALL(C1:C15, [1])

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

A0

B-1

C2

D15

Attempts:

3 left

💡 Hint

Common Mistakes

Using 0 or negative numbers causes errors.

Using 15 returns the 15th smallest, which might be out of range.

The second argument must be a positive integer. Using 2 returns the 2nd smallest number.

4fill in blank

hard

Fill both blanks to create a formula that finds the 4th largest number in D1:D30.

Excel

=LARGE([1], [2])

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

AD1:D30

BD1:D20

C4

D3

Attempts:

3 left

💡 Hint

Common Mistakes

Using a smaller range than needed.

Using the wrong position number.

The formula =LARGE(D1:D30, 4) returns the 4th largest number in the range D1:D30.

5fill in blank

hard

Fill both blanks to create a formula that finds the 5th smallest number in E5:E50.

Excel

=SMALL([1], [2])

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

AE5:E50

B5

C4

DE1:E50

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong range.

Using the wrong position number.

The formula =SMALL(E5:E50, 5) returns the 5th smallest number in the range E5:E50.

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