MIN and MAX functions in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to know the lowest and highest sales amounts from last month to understand sales performance.

📊 Data: You have a list of daily sales amounts for the last month in a column.

🎯 Deliverable: Create a simple report showing the minimum and maximum sales values from the data.

Sample Data

Date	Sales Amount
2024-05-01	250
2024-05-02	300
2024-05-03	150
2024-05-04	400
2024-05-05	350
2024-05-06	200
2024-05-07	450
2024-05-08	100
2024-05-09	500
2024-05-10	300

1

Step 1: Select a cell where you want to show the minimum sales amount.

Click on an empty cell, for example, C12.

Expected Result

Cell C12 is selected and ready for formula input.

2

Step 2: Enter the formula to find the minimum sales amount from the Sales Amount column.

=MIN(B2:B11)

Expected Result

The cell shows 100, which is the smallest sales amount in the list.

3

Step 3: Select another cell to show the maximum sales amount.

Click on an empty cell, for example, C13.

Expected Result

Cell C13 is selected and ready for formula input.

4

Step 4: Enter the formula to find the maximum sales amount from the Sales Amount column.

=MAX(B2:B11)

Expected Result

The cell shows 500, which is the largest sales amount in the list.

5

Step 5: Label the results for clarity.

In cell B12, type 'Minimum Sales'; in cell B13, type 'Maximum Sales'.

Expected Result

Cells B12 and B13 show labels next to the minimum and maximum values.

Final Result

Date       | Sales Amount
-------------------------
2024-05-01 | 250
2024-05-02 | 300
2024-05-03 | 150
2024-05-04 | 400
2024-05-05 | 350
2024-05-06 | 200
2024-05-07 | 450
2024-05-08 | 100
2024-05-09 | 500
2024-05-10 | 300

Minimum Sales: 100
Maximum Sales: 500

✓The lowest sales amount last month was 100.

✓The highest sales amount last month was 500.

Bonus Challenge

Create a formula to find the difference between the maximum and minimum sales amounts.

Show Hint

Use the formula =MAX(range) - MIN(range) with the same sales data range.

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