MIN and MAX functions in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Find the minimum value ignoring text

Given the range A1:A5 contains the values {10, "apple", 5, 20, "banana"}, what is the result of the formula =MIN(A1:A5)?

A 5

B apple

C 10

D Error

💡 Hint

MIN ignores text values and only considers numbers.

The MIN function in Google Sheets ignores text values and finds the smallest number in the range. Here, it ignores "apple" and "banana" and returns 5.

📊 Formula Result

intermediate

Find the maximum value with negative numbers

If the range B1:B4 contains the values {-3, -10, -1, -7}, what does the formula =MAX(B1:B4) return?

A 0

B -10

C -1

D Error

💡 Hint

MAX finds the largest number, even if all are negative.

Among negative numbers, the one closest to zero is the largest. Here, -1 is the largest number.

❓ Function Choice

advanced

Choose the correct formula to find the max excluding zeros

You want to find the maximum value in range C1:C6 but ignore any zeros. Which formula will correctly do this?

A =MAX(IF(C1:C6=0, "", C1:C6))

B =MAX(FILTER(C1:C6, C1:C6<>0))

C =MAX(C1:C6)

D =MAX(IF(C1:C6>0, C1:C6))

💡 Hint

Use FILTER to exclude zeros before finding MAX.

Option B uses FILTER to exclude zeros, then MAX finds the largest value. Option B includes zeros. Option B returns text which causes error. Option B is incomplete and causes error.

🎯 Scenario

advanced

Calculate the range (difference between max and min) ignoring blanks

You have numbers in D1:D8 with some blank cells. Which formula correctly calculates the difference between the maximum and minimum values ignoring blanks?

A =MAX(FILTER(D1:D8, D1:D8<>""))-MIN(FILTER(D1:D8, D1:D8<>""))

B =MAX(D1:D8)-MIN(D1:D8)+COUNTBLANK(D1:D8)

C =MAX(D1:D8)-MIN(IF(D1:D8<>"", D1:D8))

D =MAX(D1:D8)-MIN(D1:D8)

💡 Hint

Use FILTER to exclude blanks before calculating min and max.

Option A filters out blank cells before calculating MAX and MIN, so the difference is accurate. Option A includes blanks which are ignored but can cause issues if blanks are text. Option A is an array formula missing ARRAYFORMULA wrapper and will error. Option A adds blank count incorrectly.

❓ data_analysis

expert

Analyze output of nested MIN and MAX with conditions

Given the range E1:E7 contains {4, 9, 2, 15, 7, 3, 10}, what is the output of the formula =MIN(MAX(E1:E7), 8)?

A Error

B 15

C 4

D 8

💡 Hint

MAX(E1:E7) finds the largest number, then MIN compares it with 8.

MAX(E1:E7) is 15. MIN(15, 8) returns the smaller value, which is 8.

Practice

1. What does the MIN function do in Google Sheets?

easy

A. Finds the smallest number in a range or list

B. Finds the largest number in a range or list

C. Adds all numbers in a range or list

D. Counts how many numbers are in a range or list

Solution

1. Step 1: Understand the purpose of MIN

   The MIN function looks through numbers and picks the smallest one.

2. Step 2: Compare with other functions

   Unlike MAX which finds the largest, MIN finds the smallest number.

3. Final Answer:

   Finds the smallest number in a range or list -> Option A

4. Quick Check:

   MIN = smallest number [OK]

Hint: MIN always returns the smallest number [OK]

Common Mistakes:

• Confusing MIN with MAX
• Thinking MIN adds numbers
• Assuming MIN counts numbers

2. Which of these is the correct way to write a formula to find the maximum value in cells A1 to A5?

easy

A. =MAX[A1:A5]

B. =MAX(A1:A5)

C. MAX(A1:A5)

D. =max(A1 to A5)

Solution

1. Step 1: Check correct formula syntax

   Google Sheets formulas start with = and use parentheses for arguments.

2. Step 2: Identify correct MAX usage

   =MAX(A1:A5) correctly uses =, function name in uppercase, and range in parentheses.

3. Final Answer:

   =MAX(A1:A5) -> Option B

4. Quick Check:

   Correct formula syntax =MAX(range) [OK]

Hint: Always start formulas with = and use parentheses [OK]

Common Mistakes:

• Using square brackets instead of parentheses
• Omitting the = sign
• Writing function name in lowercase without =

3. Given the values in cells B1 to B4 as 10, 25, 7, and 18 respectively, what will =MIN(B1:B4) return?

medium

A. 25

B. 10

C. 18

D. 7

Solution

1. Step 1: List the values in B1 to B4

   The values are 10, 25, 7, and 18.

2. Step 2: Find the smallest value

   Among these, 7 is the smallest number.

3. Final Answer:

   7 -> Option D

4. Quick Check:

   MIN(10,25,7,18) = 7 [OK]

Hint: MIN picks the smallest number in the range [OK]

Common Mistakes:

• Choosing the first number instead of the smallest
• Confusing MIN with MAX
• Selecting the average instead

4. You wrote the formula =MAX(B1:B5 but it shows an error. What is the problem?

medium

A. Cells B1 to B5 are empty

B. MAX function cannot use ranges

C. Missing closing parenthesis ")"

D. Formula should start with MIN, not MAX

Solution

1. Step 1: Check formula syntax

   The formula is missing the closing parenthesis ")" at the end.

2. Step 2: Understand function requirements

   MAX requires parentheses around its argument range to work correctly.

3. Final Answer:

   Missing closing parenthesis ")" -> Option C

4. Quick Check:

   Formula syntax error =MAX(range) needs closing ) [OK]

Hint: Always close parentheses in formulas [OK]

Common Mistakes:

• Forgetting to close parentheses
• Thinking MAX can't use ranges
• Confusing MAX with MIN

5. You have sales numbers in cells C1 to C6: 150, 200, 0, 175, 200, 125. You want to find the highest sale excluding zeros. Which formula will give the correct result?

hard

A. =MAX(FILTER(C1:C6, C1:C6>0))

B. =MAX(C1:C6>0)

C. =MIN(C1:C6)

D. =MAX(IF(C1:C6=0, "", C1:C6))

Solution

1. Step 1: Understand the problem

   You want the highest number but ignore zeros.

2. Step 2: Analyze each formula

   B: =MAX(C1:C6>0) incorrectly places condition inside MAX, returns max of booleans (1). A: FILTER selects only numbers > 0, then MAX finds highest among them. C: MIN finds smallest, not highest. D: IF replaces zeros with empty text, but MAX with text causes error.

3. Step 3: Choose the correct formula

   =MAX(FILTER(C1:C6, C1:C6>0)) correctly filters out zeros before finding MAX.

4. Final Answer:

   =MAX(FILTER(C1:C6, C1:C6>0)) -> Option A

5. Quick Check:

   Filter out zeros, then MAX = highest non-zero [OK]

Hint: Use FILTER to exclude zeros before MAX [OK]

Common Mistakes:

• Using MAX without filtering zeros
• Using MIN instead of MAX
• Replacing zeros with text causing errors