LARGE and SMALL in Excel - Cell-by-Cell Formula Trace

Sample Data

This table shows a list of scores in cells A2 to A6. Column B contains formulas to find the 2nd largest and 3rd smallest scores.

Cell	Value
A1	Scores
A2	50
A3	80
A4	70
A5	90
A6	60
B1	Formula
B2	=LARGE(A2:A6, 2)
B3	=SMALL(A2:A6, 3)

Formula Trace

=LARGE(A2:A6, 2)

Step 1: A2:A6

Step 2: Sort values in descending order

Step 3: Select 2nd value in sorted list

Cell Reference Map

    A      B
1 Scores  Formula
2  50     -> LARGE(A2:A6, 2)
3  80     -> SMALL(A2:A6, 3)
4  70
5  90
6  60

The formulas in B2 and B3 refer to the range A2:A6 which contains the scores.

Result

    A      B
1 Scores  Formula
2  50     80
3  80     70
4  70
5  90
6  60

Cell B2 shows 80, the 2nd largest score. Cell B3 shows 70, the 3rd smallest score.

Sheet Trace Quiz - 3 Questions

What does the formula =LARGE(A2:A6, 2) return?

A 70

B 80

C 90

D 60

Key Result

LARGE(range, k) returns the kth largest value; SMALL(range, k) returns the kth smallest value.

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