IFERROR for error handling in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants you to calculate the sales per unit for each product. However, some products have zero units sold, which causes errors in the calculation. You need to handle these errors gracefully.

📊 Data: You have a table with Product names, Total Sales amount, and Units Sold.

🎯 Deliverable: Create a new column that calculates Sales per Unit. If Units Sold is zero or missing, show 'No Sales' instead of an error.

Sample Data

Product	Total Sales	Units Sold
Apples	500	50
Bananas	300	0
Cherries	450	30
Dates	0	0
Elderberries	600	40
Figs	350

1

Step 1: Create a new column named 'Sales per Unit' next to 'Units Sold'.

Expected Result

A new empty column ready for formulas.

2

Step 2: In the first cell under 'Sales per Unit' (e.g., D2), enter a formula to divide Total Sales by Units Sold, but handle errors using IFERROR.

=IFERROR(B2/C2, "No Sales")

Expected Result

For Apples, the formula calculates 500/50 = 10.

3

Step 3: Copy the formula down the entire 'Sales per Unit' column for all products.

Drag the fill handle from D2 down to D7.

Expected Result

Bananas and Dates show 'No Sales' because Units Sold is zero. Figs show 'No Sales' because Units Sold is blank.

4

Step 4: Format the 'Sales per Unit' column to show numbers with two decimal places where applicable.

Select column D, then Format Cells > Number > 2 decimal places.

Expected Result

Numeric results show with two decimals, text 'No Sales' remains unchanged.

Final Result

Product     Total Sales  Units Sold  Sales per Unit
--------------------------------------------------
Apples      500          50          10.00
Bananas     300          0           No Sales
Cherries    450          30          15.00
Dates       0            0           No Sales
Elderberries600          40          15.00
Figs        350                      No Sales

✓Products with zero or missing units sold show 'No Sales' instead of errors.

✓Sales per Unit is correctly calculated for products with valid units sold.

✓IFERROR helps keep the report clean and easy to read.

Bonus Challenge

Modify the formula to show 'Check Data' if Units Sold is blank, and 'No Sales' if Units Sold is zero.

Show Hint

Use IF and ISBLANK inside IFERROR to check for blank cells before dividing.