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.