AVERAGEIF and AVERAGEIFS in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants to understand the average sales amounts for different product categories and regions to identify where sales are strongest.

📊 Data: You have a sales data table with columns: Date, Region, Product Category, Sales Amount.

🎯 Deliverable: Create a summary report showing average sales by product category and average sales by product category within each region.

Sample Data

Date	Region	Product Category	Sales Amount
2024-01-05	North	Electronics	1200
2024-01-07	South	Clothing	800
2024-01-10	East	Electronics	950
2024-01-12	West	Clothing	700
2024-01-15	North	Clothing	650
2024-01-18	South	Electronics	1100
2024-01-20	East	Clothing	720
2024-01-22	West	Electronics	1300
2024-01-25	North	Electronics	1150
2024-01-28	South	Clothing	780

1

Step 1: Create a list of unique product categories to analyze average sales.

Manually list: Electronics, Clothing

Expected Result

Two rows with 'Electronics' and 'Clothing'

2

Step 2: Calculate the average sales amount for each product category using AVERAGEIF.

For Electronics: =AVERAGEIF(C2:C11, "Electronics", D2:D11) For Clothing: =AVERAGEIF(C2:C11, "Clothing", D2:D11)

Expected Result

Electronics average: 1140 Clothing average: 730

3

Step 3: Create a list of regions to analyze average sales by product category within each region.

Manually list: North, South, East, West

Expected Result

Four rows with 'North', 'South', 'East', 'West'

4

Step 4: Calculate average sales for Electronics in each region using AVERAGEIFS.

For North Electronics: =AVERAGEIFS(D2:D11, C2:C11, "Electronics", B2:B11, "North") For South Electronics: =AVERAGEIFS(D2:D11, C2:C11, "Electronics", B2:B11, "South") For East Electronics: =AVERAGEIFS(D2:D11, C2:C11, "Electronics", B2:B11, "East") For West Electronics: =AVERAGEIFS(D2:D11, C2:C11, "Electronics", B2:B11, "West")

Expected Result

North Electronics average: 1175 South Electronics average: 1100 East Electronics average: 950 West Electronics average: 1300

5

Step 5: Calculate average sales for Clothing in each region using AVERAGEIFS.

For North Clothing: =AVERAGEIFS(D2:D11, C2:C11, "Clothing", B2:B11, "North") For South Clothing: =AVERAGEIFS(D2:D11, C2:C11, "Clothing", B2:B11, "South") For East Clothing: =AVERAGEIFS(D2:D11, C2:C11, "Clothing", B2:B11, "East") For West Clothing: =AVERAGEIFS(D2:D11, C2:C11, "Clothing", B2:B11, "West")

Expected Result

North Clothing average: 650 South Clothing average: 790 East Clothing average: 720 West Clothing average: 700

Final Result

Average Sales by Product Category
-------------------------------
Electronics: 1140
Clothing: 730

Average Sales by Product Category and Region
-------------------------------------------
Region   Electronics   Clothing
North    1175          650
South    1100          790
East     950           720
West     1300          700

✓Electronics have higher average sales than Clothing overall.

✓West region has the highest average sales for Electronics.

✓South region leads in average sales for Clothing.

✓North region has strong Electronics sales but lower Clothing sales.

Bonus Challenge

Create a dynamic summary table that allows selecting a product category from a dropdown and shows average sales by region for that category.

Show Hint

Use Data Validation for dropdown and AVERAGEIFS with cell references for criteria.