LET function for named calculations in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to calculate the total profit for each product after applying discounts and costs, using a clear and efficient formula.

📊 Data: You have a table with Product names, Sales Price, Discount %, and Cost per unit.

🎯 Deliverable: Create a new column that uses the LET function to calculate the profit per product, naming intermediate calculations for clarity.

Sample Data

Product	Sales Price	Discount %	Cost
Notebook	20	10	12
Pen	5	5	2
Backpack	50	20	30
Calculator	30	15	18
Ruler	8	0	3
Marker	12	10	6
Folder	15	5	7
Glue	7	0	2

1

Step 1: Insert a new column next to the Cost column and name it 'Profit'.

Expected Result

A new column labeled 'Profit' is ready for formulas.

2

Step 2: In the first cell under 'Profit' (e.g., E2), enter the LET formula to calculate profit by naming the discounted price and profit calculation.

=LET(discountedPrice, B2*(1 - C2/100), profit, discountedPrice - D2, profit)

Expected Result

For Notebook, profit is 6 (20 * 0.9 = 18; 18 - 12 = 6).

3

Step 3: Copy the formula down the 'Profit' column for all products.

Drag the fill handle from E2 down to E9.

Expected Result

Profit values calculated for all products: Pen=2.75, Backpack=10, Calculator=7.5, Ruler=5, Marker=4.8, Folder=7.25, Glue=5.

4

Step 4: Format the 'Profit' column as currency for better readability.

Select the 'Profit' column and apply Currency format.

Expected Result

Profit values display with currency symbol and two decimals.

Final Result

Product    Sales Price  Discount %  Cost  Profit
------------------------------------------------
Notebook   20           10          12    $6.00
Pen        5            5           2     $2.75
Backpack   50           20          30    $10.00
Calculator 30           15          18    $7.50
Ruler      8            0           3     $5.00
Marker     12           10          6     $4.80
Folder     15           5           7     $7.25
Glue       7            0           2     $5.00

✓Using LET makes the formula easier to read by naming intermediate steps.

✓Profit varies by product depending on discount and cost.

✓Backpack has the highest profit of $10.00 after discount and cost.

Bonus Challenge

Modify the LET formula to also calculate and show the profit margin percentage (profit divided by discounted price) in a new column.

Show Hint

Use LET to name discountedPrice and profit, then calculate margin as profit / discountedPrice.