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.