Error-proof a complex formula chain
IntermediateMarkup percentage is a common business metric: (selling price minus cost) divided by cost. When your data comes from a lookup table, a plain formula can break in multiple ways. If a product code is missing from the cost table, the lookup returns #N/A. If a product has a zero cost, the division fails with #DIV/0!. By layering error-handling functions, you can catch each error type and show a meaningful message instead of a cryptic error code.
In this exercise, you have a product cost table in A1:B7 and a sales log in A9:E15. The sales log has an Order ID, a Product Code, a Selling Price, and two blank columns for your formulas: Markup % (column D) and Status (column E).
Step 1: Write the raw markup formula
In D10, write a formula that looks up the product's cost from the cost table and calculates the markup percentage:
(Selling Price - Cost) / Cost
Use VLOOKUP with an exact match to find the cost. Once D10 works, copy the formula down to D15.
Some rows will naturally produce errors. That is expected. One product code does not appear in the cost table, and one product has a unit cost of zero. You will fix those in the next step.
Step 2: Write the error-proofed version
In E10, build a formula that uses the result in D10 and returns the same markup result for valid rows, but catches two different error types and returns a specific message for each:
- If D10 shows #N/A because the product code cannot be found in the cost table, return "Product not found"
- If D10 shows another error because the cost is zero, return "Zero cost item"
The distinction between the two errors matters: one means the product is not in your database, the other means the product is free but still sold. Use a targeted error handler for the missing product case first, then wrap that result in a broader error handler for the remaining division error.
Once E10 is correct, copy the formula down to E15.
When you are done, column E should show markup percentages for valid products and a descriptive message for each of the two error scenarios.
Need some help?
Hint 1
The raw markup formula in column D uses VLOOKUP to find the cost, then calculates (selling price minus cost) divided by cost. You'll need to reference the lookup table with absolute references so the formula can be copied down.
Hint 2
In column E, reference the raw result from column D. Use IFNA to turn #N/A into "Product not found", then wrap that with IFERROR to turn the remaining #DIV/0! error into "Zero cost item".
Error-proof a complex formula chain
IntermediateMarkup percentage is a common business metric: (selling price minus cost) divided by cost. When your data comes from a lookup table, a plain formula can break in multiple ways. If a product code is missing from the cost table, the lookup returns #N/A. If a product has a zero cost, the division fails with #DIV/0!. By layering error-handling functions, you can catch each error type and show a meaningful message instead of a cryptic error code.
In this exercise, you have a product cost table in A1:B7 and a sales log in A9:E15. The sales log has an Order ID, a Product Code, a Selling Price, and two blank columns for your formulas: Markup % (column D) and Status (column E).
Step 1: Write the raw markup formula
In D10, write a formula that looks up the product's cost from the cost table and calculates the markup percentage:
(Selling Price - Cost) / Cost
Use VLOOKUP with an exact match to find the cost. Once D10 works, copy the formula down to D15.
Some rows will naturally produce errors. That is expected. One product code does not appear in the cost table, and one product has a unit cost of zero. You will fix those in the next step.
Step 2: Write the error-proofed version
In E10, build a formula that uses the result in D10 and returns the same markup result for valid rows, but catches two different error types and returns a specific message for each:
- If D10 shows #N/A because the product code cannot be found in the cost table, return "Product not found"
- If D10 shows another error because the cost is zero, return "Zero cost item"
The distinction between the two errors matters: one means the product is not in your database, the other means the product is free but still sold. Use a targeted error handler for the missing product case first, then wrap that result in a broader error handler for the remaining division error.
Once E10 is correct, copy the formula down to E15.
When you are done, column E should show markup percentages for valid products and a descriptive message for each of the two error scenarios.
Need some help?
Hint 1
The raw markup formula in column D uses VLOOKUP to find the cost, then calculates (selling price minus cost) divided by cost. You'll need to reference the lookup table with absolute references so the formula can be copied down.
Hint 2
In column E, reference the raw result from column D. Use IFNA to turn #N/A into "Product not found", then wrap that with IFERROR to turn the remaining #DIV/0! error into "Zero cost item".