Value fields and aggregation in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a summary report showing total sales and average sales per product category for the last quarter.

📊 Data: You have a sales data table with columns: Date, Product Category, Product Name, Units Sold, and Sales Amount.

🎯 Deliverable: Create a summary table that shows each Product Category with the total sales amount and average sales amount.

Sample Data

Date	Product Category	Product Name	Units Sold	Sales Amount
2024-01-05	Electronics	Headphones	10	500
2024-01-15	Electronics	Smartphone	5	2500
2024-02-10	Home Appliances	Blender	8	400
2024-02-20	Home Appliances	Microwave	3	900
2024-03-05	Furniture	Desk	2	600
2024-03-15	Furniture	Chair	6	300
2024-03-25	Electronics	Tablet	4	1200
2024-03-30	Home Appliances	Toaster	7	350

1

Step 1: Create a list of unique Product Categories in a new column starting at cell G2.

In G2, enter: =UNIQUE(B2:B9)

Expected Result

G2:G4 will show Electronics, Home Appliances, Furniture

2

Step 2: Calculate total sales amount for each Product Category next to the unique list in column H.

In H2, enter: =SUMIF(B$2:B$9, G2, E$2:E$9) and copy down to H4

Expected Result

H2=4200, H3=1650, H4=900

3

Step 3: Calculate average sales amount per transaction for each Product Category in column I.

In I2, enter: =AVERAGEIF(B$2:B$9, G2, E$2:E$9) and copy down to I4

Expected Result

I2=1400, I3=550, I4=450

4

Step 4: Format the summary table with headers in row 1: G1='Product Category', H1='Total Sales', I1='Average Sales'.

Manually type headers in G1, H1, and I1.

Expected Result

Headers appear above the summary data.

Final Result

Product Category | Total Sales | Average Sales
------------------------------------------------
Electronics      | 4200        | 1400
Home Appliances  | 1650        | 550
Furniture        | 900         | 450

✓Electronics has the highest total sales and average sales per transaction.

✓Home Appliances has moderate sales with average sales lower than Electronics.

✓Furniture has the lowest total and average sales among the categories.

Bonus Challenge

Add a column to calculate the total units sold per Product Category.

Show Hint

Use SUMIF with the Units Sold column similar to total sales calculation.

Practice

1. What does the SUM function do in Excel when applied to a range of numbers?

easy

A. Calculates the average of the numbers in the range

B. Counts how many numbers are in the range

C. Finds the largest number in the range

D. Adds all the numbers in the range together

Solution

1. Step 1: Understand the purpose of SUM

   The SUM function adds all numeric values in the selected range.

2. Step 2: Compare with other functions

   COUNT counts numbers, MAX finds largest, AVERAGE calculates mean, so SUM is addition.

3. Final Answer:

   Adds all the numbers in the range together -> Option D

4. Quick Check:

   SUM = addition [OK]

Hint: SUM always adds numbers, not counts or averages [OK]

Common Mistakes:

• Confusing SUM with COUNT
• Thinking SUM finds max or average
• Using SUM on text values expecting count

2. Which of these formulas correctly counts the number of numeric entries in cells A1 to A5?

easy

A. =COUNT(A1:A5)

B. =MAX(A1:A5)

C. =AVERAGE(A1:A5)

D. =SUM(A1:A5)

Solution

1. Step 1: Identify function purpose

   COUNT counts numeric entries in a range, ignoring text or blanks.

2. Step 2: Check other options

   SUM adds numbers, AVERAGE calculates mean, MAX finds largest number, so only COUNT counts numbers.

3. Final Answer:

   =COUNT(A1:A5) -> Option A

4. Quick Check:

   COUNT counts numbers [OK]

Hint: COUNT counts numbers, SUM adds them [OK]

Common Mistakes:

• Using SUM instead of COUNT to count numbers
• Confusing COUNT with COUNTA (which counts all non-empty)
• Using MAX or AVERAGE expecting count

3. Given the values in cells B1 to B4 as 10, 20, 30, and 40, what is the result of =AVERAGE(B1:B4)?

medium

A. 25

B. 20

C. 30

D. 100

Solution

1. Step 1: Sum the values in B1:B4

   10 + 20 + 30 + 40 = 100

2. Step 2: Divide sum by count of numbers

   There are 4 numbers, so 100 ÷ 4 = 25

3. Final Answer:

   25 -> Option A

4. Quick Check:

   Average = sum/count = 25 [OK]

Hint: Average = total sum divided by count [OK]

Common Mistakes:

• Adding only some numbers
• Dividing by wrong count
• Confusing average with sum or max

4. You want to find the smallest number in range C1:C5, but your formula =MIN(C1:C5 gives an error. What is the fix?

medium

A. Change range to C1-C5

B. Replace MIN with MAX function

C. Add the missing closing parenthesis: =MIN(C1:C5)

D. Use SUM instead of MIN

Solution

1. Step 1: Identify syntax error

   The formula is missing a closing parenthesis, causing a syntax error.

2. Step 2: Correct the formula

   Adding the closing parenthesis fixes the syntax: =MIN(C1:C5)

3. Final Answer:

   Add the missing closing parenthesis: =MIN(C1:C5) -> Option C

4. Quick Check:

   All functions need matching parentheses [OK]

Hint: Check for missing parentheses in formulas [OK]

Common Mistakes:

• Using dash (-) instead of colon (:) for range
• Replacing MIN with unrelated functions
• Ignoring syntax errors

5. You have sales data in cells D1 to D6: 100, 200, 0, 150, blank, and 300. Which formula correctly calculates the average sales ignoring blank and zero values?

hard

A. =AVERAGE(D1:D6)

B. =SUMIF(D1:D6,">0")/COUNTIF(D1:D6,">0")

C. =SUM(D1:D6)/COUNT(D1:D6)

D. =MAX(D1:D6)

Solution

1. Step 1: Understand the problem

   We want average ignoring zeros and blanks, so only values > 0 count.

2. Step 2: Analyze each formula

   A and C include zeros and blanks; D finds max, not average. B sums values > 0 and divides by count of values > 0, correctly ignoring zeros and blanks.

3. Final Answer:

   =SUMIF(D1:D6,">0")/COUNTIF(D1:D6,">0") -> Option B

4. Quick Check:

   Use SUMIF and COUNTIF to filter values [OK]

Hint: Use SUMIF and COUNTIF to exclude zeros/blanks [OK]

Common Mistakes:

• Using AVERAGE which includes zeros
• Dividing SUM by COUNT including zeros
• Using MAX instead of average