Why advanced formulas solve complex problems in Excel - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants a detailed report that shows total sales, average sales per product category, and identifies the top-selling product in each category for the last quarter.

📊 Data: You have sales data including Date, Product Name, Category, Units Sold, and Unit Price for the last three months.

🎯 Deliverable: Create a summary report using advanced formulas that calculates total sales per category, average sales per product, and highlights the top-selling product in each category.

Sample Data

Date	Product Name	Category	Units Sold	Unit Price
2024-03-01	Notebook	Stationery	120	2.5
2024-03-05	Pen	Stationery	200	1.2
2024-03-10	Stapler	Stationery	75	5.0
2024-04-02	Chair	Furniture	15	45.0
2024-04-15	Desk	Furniture	10	120.0
2024-04-20	Table Lamp	Furniture	20	30.0
2024-05-01	Coffee Mug	Kitchenware	150	3.5
2024-05-10	Plate Set	Kitchenware	40	25.0
2024-05-15	Knife Set	Kitchenware	30	45.0

1

Step 1: Add a new column 'Total Sales' to calculate sales per row by multiplying Units Sold by Unit Price.

=D2*E2

Expected Result

For row 2 (Notebook), Total Sales = 120 * 2.5 = 300

2

Step 2: Calculate total sales per category using SUMIF formula.

=SUMIF(C:C, "Stationery", F:F)

Expected Result

Total sales for Stationery = 300 + 240 + 375 = 915

3

Step 3: Calculate average sales per product category using AVERAGEIF formula.

=AVERAGEIF(C:C, "Furniture", F:F)

Expected Result

Average sales for Furniture = (675 + 1200 + 600) / 3 = 825

4

Step 4: Identify the top-selling product in each category using MAXIFS and INDEX-MATCH formulas.

Top sales in Stationery: =MAXIFS(F:F, C:C, "Stationery") Top product: =INDEX(B:B, MATCH(MAXIFS(F:F, C:C, "Stationery"), F:F, 0))

Expected Result

Top-selling product in Stationery is Stapler with sales 375

5

Step 5: Repeat step 4 for Furniture and Kitchenware categories to find their top-selling products.

Furniture top sales: =MAXIFS(F:F, C:C, "Furniture") Furniture top product: =INDEX(B:B, MATCH(MAXIFS(F:F, C:C, "Furniture"), F:F, 0)) Kitchenware top sales: =MAXIFS(F:F, C:C, "Kitchenware") Kitchenware top product: =INDEX(B:B, MATCH(MAXIFS(F:F, C:C, "Kitchenware"), F:F, 0))

Expected Result

Furniture top product: Desk with sales 1200 Kitchenware top product: Knife Set with sales 1350

6

Step 6: Create a summary table listing each category, total sales, average sales, and top-selling product.

Use the results from previous steps to fill the summary table manually or with cell references.

Expected Result

Summary table shows Stationery: Total 915, Average 305, Top product Stapler Furniture: Total 2475, Average 825, Top product Desk Kitchenware: Total 2875, Average 958.33, Top product Knife Set

Final Result

Category    | Total Sales | Average Sales | Top-Selling Product
-------------------------------------------------------------
Stationery  | 915         | 305           | Stapler
Furniture   | 2475        | 825           | Desk
Kitchenware | 2875        | 958.33        | Knife Set

✓Kitchenware category has the highest total sales and average sales per product.

✓Stapler is the top-selling product in Stationery despite lower units sold because of higher unit price.

✓Knife Set leads Kitchenware sales due to high unit price despite lower units sold.

Bonus Challenge

Create a dynamic dropdown to select a category and display its total sales, average sales, and top product automatically.

Show Hint

Use Data Validation for dropdown and formulas with cell references to update summary based on selection.

Practice

1. Why do advanced formulas help solve complex problems in Excel?

easy

A. They make the spreadsheet look colorful and pretty.

B. They only work with small amounts of data.

C. They slow down the spreadsheet by adding extra calculations.

D. They combine multiple steps into one formula to save time.

Solution

1. Step 1: Understand what advanced formulas do

   Advanced formulas combine many functions to perform complex calculations in one step.

2. Step 2: Identify the benefit of combining steps

   Combining steps saves time and reduces errors compared to doing many simple formulas separately.

3. Final Answer:

   They combine multiple steps into one formula to save time. -> Option D

4. Quick Check:

   Advanced formulas save time [OK]

Hint: Think: fewer formulas, faster results [OK]

Common Mistakes:

• Confusing formatting with formula power
• Assuming advanced formulas slow down work
• Believing they only work on small data

2. Which of these is the correct syntax to combine IF and AND functions in Excel?

easy

A. =IF(AND(A1>10; B1<5), "Yes", "No")

B. =IF(AND(A1>10, B1<5), "Yes", "No")

C. =IF(AND A1>10, B1<5, "Yes", "No")

D. =IF(AND(A1>10 B1<5), "Yes", "No")

Solution

1. Step 1: Check the correct use of AND inside IF

   AND requires parentheses around conditions separated by commas, like AND(condition1, condition2).

2. Step 2: Verify IF syntax with AND

   IF syntax is IF(logical_test, value_if_true, value_if_false). =IF(AND(A1>10, B1<5), "Yes", "No") matches this exactly.

3. Final Answer:

   =IF(AND(A1>10, B1<5), "Yes", "No") -> Option B

4. Quick Check:

   Correct IF + AND syntax [OK]

Hint: Use commas inside AND and wrap conditions in parentheses [OK]

Common Mistakes:

• Using semicolons instead of commas
• Missing parentheses around AND conditions
• Not separating conditions with commas

3. What is the result of this formula if A1=5, B1=3, and C1=8?
=IF(AND(A1>B1, C1>10), "Pass", "Fail")

medium

A. "Fail"

B. "Pass"

C. TRUE

D. FALSE

Solution

1. Step 1: Evaluate the AND condition

   Check if A1 > B1: 5 > 3 is TRUE. Check if C1 > 10: 8 > 10 is FALSE. AND(TRUE, FALSE) = FALSE.

2. Step 2: Apply IF based on AND result

   Since AND is FALSE, IF returns the value_if_false, which is "Fail".

3. Final Answer:

   "Fail" -> Option A

4. Quick Check:

   AND condition FALSE means IF returns "Fail" [OK]

Hint: Check each condition inside AND separately [OK]

Common Mistakes:

• Assuming AND returns TRUE if one condition is true
• Confusing TRUE/FALSE with text results
• Ignoring the value_if_false part of IF

4. This formula is meant to return "High" if A1 is greater than 100 and "Low" otherwise:
=IF(AND(A1>100, "High", "Low"))
What is wrong with it?

medium

A. The IF function has the true/false values inside AND instead of IF.

B. The IF function is missing the logical test argument.

C. The AND function is missing a second condition.

D. The formula uses commas instead of semicolons.

Solution

1. Step 1: Analyze the formula structure

   AND should only contain logical conditions, but here it includes "High" and "Low" which are text outputs.

2. Step 2: Identify correct placement of true/false values

   True/false outputs belong as the 2nd and 3rd arguments of IF, not inside AND.

3. Final Answer:

   The IF function has the true/false values inside AND instead of IF. -> Option A

4. Quick Check:

   True/false values go in IF, not AND [OK]

Hint: Put true/false outputs only in IF, not inside AND [OK]

Common Mistakes:

• Putting outputs inside AND function
• Confusing argument order in IF
• Thinking AND can return text values

5. You want to create a formula that returns "Bonus" if sales in A1 are above 1000 and the region in B1 is "East" or "West". Which formula correctly solves this complex condition?

hard

A. =IF(AND(A1>1000, OR(B1="East"; B1="West")), "Bonus", "No Bonus")

B. =IF(OR(A1>1000, AND(B1="East", B1="West")), "Bonus", "No Bonus")

C. =IF(AND(A1>1000, OR(B1="East", B1="West")), "Bonus", "No Bonus")

D. =IF(AND(A1>1000, B1="East", B1="West"), "Bonus", "No Bonus")

Solution

1. Step 1: Understand the condition logic

   Bonus if sales > 1000 AND region is either East OR West.

2. Step 2: Check formula structure

   =IF(AND(A1>1000, OR(B1="East", B1="West")), "Bonus", "No Bonus") uses AND for sales and OR for region correctly with commas separating arguments.

3. Step 3: Identify errors in other options

   =IF(OR(A1>1000, AND(B1="East", B1="West")), "Bonus", "No Bonus") uses OR incorrectly combining sales and region. =IF(AND(A1>1000, B1="East", B1="West"), "Bonus", "No Bonus") requires region to be both East and West simultaneously (impossible). =IF(AND(A1>1000, OR(B1="East"; B1="West")), "Bonus", "No Bonus") uses semicolons which may cause errors in some locales.

4. Final Answer:

   =IF(AND(A1>1000, OR(B1="East", B1="West")), "Bonus", "No Bonus") -> Option C

5. Quick Check:

   AND + OR with commas = correct complex condition [OK]

Hint: Use AND for all musts, OR for any one condition [OK]

Common Mistakes:

• Mixing AND and OR incorrectly
• Using semicolons instead of commas
• Checking impossible conditions like region = East and West