LAMBDA for custom functions in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a quick way to calculate the total price after applying a discount and tax for any product sold. They want a reusable formula to avoid repeating complex calculations.

📊 Data: You have a table with product names, unit prices, quantities sold, discount rates (as decimals), and tax rates (as decimals).

🎯 Deliverable: Create a custom LAMBDA function that calculates the final price after discount and tax, then use it to calculate the total price for each product.

Sample Data

Product	Unit Price	Quantity	Discount Rate	Tax Rate
Notebook	10	5	0.1	0.07
Pen	2	20	0.05	0.07
Backpack	50	2	0.15	0.07
Calculator	25	3	0.2	0.07
Ruler	3	10	0	0.07

1

Step 1: Define a LAMBDA function named FinalPrice that takes four inputs: unit price, quantity, discount rate, and tax rate. The function should calculate the total price after applying the discount and then adding tax.

=LAMBDA(unitPrice, qty, discount, tax, (unitPrice * qty) * (1 - discount) * (1 + tax))

Expected Result

The LAMBDA function is ready to use for calculations.

2

Step 2: Name the LAMBDA function FinalPrice using the Name Manager so it can be reused in the workbook.

Use Excel's Name Manager to create a new name 'FinalPrice' with the formula from step 1.

Expected Result

FinalPrice is available as a custom function in the workbook.

3

Step 3: In a new column next to the data, calculate the final price for each product by calling the FinalPrice function with the corresponding row values.

=FinalPrice(B2, C2, D2, E2)

Expected Result

For Notebook: 10*5=50, discount 10% -> 50*0.9=45, tax 7% -> 45*1.07=48.15

4

Step 4: Copy the formula down for all rows to get the final price for each product.

Drag the formula from the first product row down to the last product row.

Expected Result

Final prices: Notebook=48.15, Pen=40.66, Backpack=90.95, Calculator=80.1, Ruler=32.1

Final Result

Product    Unit Price  Quantity  Discount Rate  Tax Rate  Final Price
---------------------------------------------------------------------
Notebook   10          5         0.1            0.07      48.15
Pen        2           20        0.05           0.07      40.66
Backpack   50          2         0.15           0.07      90.95
Calculator 25          3         0.2            0.07      80.1
Ruler      3           10        0              0.07      32.1

✓The custom LAMBDA function FinalPrice simplifies repeated calculations.

✓Final prices correctly reflect discounts and taxes for each product.

✓Using LAMBDA improves efficiency and reduces formula errors.

Bonus Challenge

Modify the FinalPrice LAMBDA function to include an optional parameter for a fixed shipping fee that adds to the final price only if provided.

Show Hint

Use the IF function inside LAMBDA to check if the shipping fee parameter is missing or zero, then add it accordingly.