Why MIN and MAX functions in Google Sheets? - Purpose & Use Cases

The Big Idea

What if you could find the smallest and largest numbers in seconds without any mistakes?

The Scenario

Imagine you have a long list of sales numbers in a spreadsheet, and you want to find the smallest and largest sales values. Doing this by scanning each number one by one is tiring and easy to mess up.

The Problem

Manually checking each number takes a lot of time and you might miss the smallest or largest value. If the list changes, you have to do it all over again, which is frustrating and error-prone.

The Solution

The MIN and MAX functions quickly find the smallest and largest numbers in a range for you. They update automatically if your data changes, saving you time and avoiding mistakes.

Before vs After

✗ Before

Look at each cell and remember the smallest and largest values manually.

✓ After

=MIN(A1:A10) and =MAX(A1:A10) to get smallest and largest values instantly

What It Enables

You can instantly know the lowest and highest numbers in your data, making decisions faster and more confidently.

Real Life Example

A store manager uses MIN and MAX to quickly find the lowest and highest daily sales in a month to understand performance trends.

Key Takeaways

MIN and MAX find smallest and largest numbers automatically.

They save time and reduce errors compared to manual checking.

They update results instantly when data changes.

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