MIN and MAX functions in Excel - 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 for each product category last month.

📊 Data: You have a table with sales data including Product Category, Product Name, and Sales Amount for each sale.

🎯 Deliverable: Create a summary table that shows each Product Category with its minimum and maximum sales amounts.

Sample Data

Product Category	Product Name	Sales Amount
Electronics	Smartphone	500
Electronics	Laptop	1200
Electronics	Headphones	150
Clothing	T-Shirt	20
Clothing	Jeans	40
Clothing	Jacket	100
Home	Blender	80
Home	Microwave	150
Home	Toaster	30

1

Step 1: Create a list of unique Product Categories in a new column, for example in cells E2 to E4: Electronics, Clothing, Home.

Manually type the unique categories or use the formula: =UNIQUE(A2:A10)

Expected Result

E2: Electronics, E3: Clothing, E4: Home

2

Step 2: Calculate the minimum sales amount for each Product Category using the MINIFS function.

=MINIFS(C2:C10, A2:A10, E2)

Expected Result

For Electronics in F2: 150 (lowest sales amount)

3

Step 3: Copy the MINIFS formula down for all categories in column F.

Copy =MINIFS(C2:C10, A2:A10, E2) down to F3 and F4

Expected Result

F3: 20 (Clothing), F4: 30 (Home)

4

Step 4: Calculate the maximum sales amount for each Product Category using the MAXIFS function.

=MAXIFS(C2:C10, A2:A10, E2)

Expected Result

For Electronics in G2: 1200 (highest sales amount)

5

Step 5: Copy the MAXIFS formula down for all categories in column G.

Copy =MAXIFS(C2:C10, A2:A10, E2) down to G3 and G4

Expected Result

G3: 100 (Clothing), G4: 150 (Home)

Final Result

Product Category | Min Sales | Max Sales
-----------------|-----------|----------
Electronics      | 150       | 1200
Clothing         | 20        | 100
Home             | 30        | 150

✓Electronics category has sales ranging from $150 to $1200.

✓Clothing category has the lowest sales at $20 and highest at $100.

✓Home category sales range between $30 and $150.

Bonus Challenge

Create a chart that visually shows the minimum and maximum sales for each product category.

Show Hint

Use a bar chart with Product Categories on the horizontal axis and Sales Amount on the vertical axis. Plot Min and Max sales as two series.

Practice

1. What does the MIN function do in Excel?

easy

A. Finds the smallest number in a range of cells

B. Finds the largest number in a range of cells

C. Adds all numbers in a range of cells

D. Counts how many numbers are in a range of cells

Solution

1. Step 1: Understand the purpose of MIN function

   The MIN function looks through a list of numbers and finds the smallest one.

2. Step 2: Compare with other options

   MAX finds the largest, SUM adds numbers, COUNT counts numbers. Only MIN finds smallest.

3. Final Answer:

   Finds the smallest number in a range of cells -> Option A

4. Quick Check:

   MIN = smallest number [OK]

Hint: MIN always returns the smallest number in your selected cells [OK]

Common Mistakes:

• Confusing MIN with MAX
• Thinking MIN adds numbers
• Using MIN to count cells

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,A5)

Solution

1. Step 1: Check correct syntax for MAX function

   MAX function uses parentheses and a colon to specify a range: MAX(A1:A5).

2. Step 2: Analyze each option

   =max(A1:A5) uses correct syntax. =MAX(A1-A5) uses minus sign, which is wrong. =Max(A1;A5) uses semicolon, which is invalid in Excel formulas. =max(A1,A5) only checks two cells, not the full range.

3. Final Answer:

   =max(A1:A5) -> Option B

4. Quick Check:

   MAX(range) uses colon for range [OK]

Hint: Use colon (:) to select a range inside MAX or MIN [OK]

Common Mistakes:

• Using minus (-) instead of colon (:)
• Using semicolon (;) instead of colon
• Selecting only two cells instead of a range

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

medium

A. 7

B. 10

C. 15

D. 30

Solution

1. Step 1: Identify the values in the range B1:B5

   The values are 10, 25, 7, 30, and 15.

2. Step 2: Find the smallest number among these values

   The smallest number is 7.

3. Final Answer:

   7 -> Option A

4. Quick Check:

   MIN(10,25,7,30,15) = 7 [OK]

Hint: MIN returns the smallest number in the selected cells [OK]

Common Mistakes:

• Choosing the first number instead of the smallest
• Confusing MIN with MAX
• Selecting a number outside the range

4. You wrote the formula =MAX(A1:A4, A5:A8) but Excel shows an error. What is the problem and how to fix it?

medium

A. MAX cannot take multiple ranges; combine ranges with a comma

B. MAX can take multiple ranges but they must be separated by semicolons, so use semicolons

C. MAX can take multiple ranges but they must be separated by commas, so formula is correct

D. MAX can take multiple ranges separated by commas, but ranges must be enclosed in parentheses separately

Solution

1. Step 1: Understand MAX function syntax for multiple ranges

   MAX can accept multiple ranges separated by commas, like MAX(A1:A4, A5:A8).

2. Step 2: Check why error might occur

   If error occurs, it might be due to regional settings using semicolons instead of commas. But standard Excel uses commas.

3. Final Answer:

   MAX can take multiple ranges but they must be separated by commas, so formula is correct -> Option C

4. Quick Check:

   MAX(range1, range2) works with commas [OK]

Hint: Separate multiple ranges with commas inside MAX [OK]

Common Mistakes:

• Using semicolons instead of commas
• Trying to add ranges with plus signs
• Not enclosing ranges properly

5. You have sales data in cells C2 to C10. You want to find the difference between the highest and lowest sales values. Which formula will give you the correct result?

hard

A. =MAX(C2:C10) * MIN(C2:C10)

B. =MIN(C2:C10) - MAX(C2:C10)

C. =MAX(C2:C10) + MIN(C2:C10)

D. =MAX(C2:C10) - MIN(C2:C10)

Solution

1. Step 1: Understand the goal

   You want the difference between the highest and lowest sales values, so subtract the smallest from the largest.

2. Step 2: Analyze each formula

   =MAX(C2:C10) - MIN(C2:C10) subtracts MIN from MAX, which is correct. =MIN(C2:C10) - MAX(C2:C10) subtracts MAX from MIN, which gives a negative or wrong result. Options A and C multiply or add, which are not differences.

3. Final Answer:

   =MAX(C2:C10) - MIN(C2:C10) -> Option D

4. Quick Check:

   Difference = largest - smallest [OK]

Hint: Subtract MIN from MAX to get range difference [OK]

Common Mistakes:

• Subtracting MAX from MIN
• Adding instead of subtracting
• Multiplying values instead of subtracting