Excel Challenge 33 : Calculate Discount Based on Category

The Problem

Discount rules usually start simple and then get messy fast. One department gets a small cut, another gets a bigger one, and suddenly the pricing sheet has to remember more than one percentage. If the wrong rate lands on the wrong row, the final price can still look normal enough to pass a quick glance, which makes the mistake harder to catch later.

This challenge keeps the focus on one real workflow: read the category, apply the matching discount, and make sure the summary at the bottom reflects the full set of discounts clearly. The flow below shows that same row-level path from category to rate to discount amount.

The Problem: Category Pricing Rules Each row needs the right discount rate before the total discount can be reviewed.

The workbook already knows the product categories, so the real job is keeping each row tied to the right rate without forcing someone to type the discount by hand.

Electronics needs the smallest cut.
Furniture gets the bigger discount.
Clothing sits between the two.

That is what makes this a useful pricing exercise instead of a simple multiplication drill. The row has to read the category first, choose the right rate, calculate the discount amount, and then feed a summary that checks the whole list.

How We Solve It

The logic starts with category matching. Excel checks the category in column B, chooses the correct percentage, and multiplies it by the base price in column C.

The challenge solution uses IFS, but the same pricing logic can also be expressed with SWITCH or a lookup table when the workbook gets larger.

Method 1: IFS by Category

Method 1: Use IFS to apply a different rate based on the category in the row.

IFS works well when you have a short set of categories and each one maps to a different formula. Excel checks the department name and uses the corresponding percentage for that row.

This is the most direct fit for the challenge because the rates are simple and the list of categories is small.

=IFS(B2="Electronics",C2*0.05,B2="Furniture",C2*0.2,B2="Clothing",C2*0.15)

Method 2: SWITCH to Return the Rate

Method 2: Use SWITCH to convert the category into a discount rate first.

SWITCH is helpful when you want the category-to-rate mapping to be easy to read. Instead of writing the multiplication directly inside each condition, you can return the rate and multiply once.

That makes the formula feel cleaner when the same price calculation happens after the category is identified.

=C2*SWITCH(B2,"Electronics",0.05,"Furniture",0.2,"Clothing",0.15)

Method 3: Use a Separate Rates Table

Method 3: Use a lookup table when discount rates need to be maintained separately.

If the rates change often, a separate table is easier to maintain. A lookup formula can fetch the correct rate from that table and keep the main pricing area cleaner.

That approach is not required here, but it is useful when category rules start growing beyond a few hard-coded options.

=C2*VLOOKUP(B2,$F$2:$G$4,2,FALSE)

Function Explanation

1. IFS

IFS checks multiple conditions in order and returns the first matching result. In this challenge, that means applying the correct discount rule based on the category in the row.

It is useful when category rules are few, clear, and handled directly in one formula.

Learn more this functionIFS

2. SWITCH

SWITCH maps one value to a matching result. Here, that means turning a category like Furniture into a discount rate like 0.2.

This can make category formulas easier to scan when the matching part is more important than the calculation part.

Learn more this functionSWITCH

3. SUM

SUM adds the calculated discount amounts together. In the summary area, it tells you the total discount value being given across all rows.

That turns the row-level calculations into a single total the business can review quickly.

Learn more this functionSUM

The most common mistake here is using the wrong decimal rate. A 15 percent discount should be written as 0.15, not 15.