Excel Formula Errors & Auditing: #N/A, #VALUE!, #REF! and How to Fix Them

What you'll build

What you will be able to do

Each lab ends with a concrete result that you build yourself and the checker confirms, cell by cell.

• Larkspur Office Supply's product margin report shows #DIV/0! twice. You find the divisor behind each error, repair the margin with an IF test, the growth rate with IFERROR, and check that the average margin is not dragged down by a fake 0%.
• Three orders in Larkspur's order sheet show #N/A. You find the three different causes (a stray space, a code that really is missing, a customer ID stored as text) and repair each one the right way.
• A supplier export has three lines that show #VALUE! (and so does the total). You find the text hiding in each calculation and repair the line totals so the purchase order adds up.
• The sales-commission sheet was copied down without dollar signs and a column was deleted. You repair the commission column with an absolute reference, the #REF! total and a scenario grid that needs mixed references.
• Larkspur's project KPI block shows #NAME? twice and #NUM! once. You correct a misspelled function, add the missing quotes around a text criterion, swap the arguments of DATEDIF and name the function a teammate meant.
• Before a supplier order file is imported, you label every line (unknown SKU, bad pack size or OK) with ISNA and ISERR, count the lines that would fail the lookup, name an error code and chart the result for the buyer.
• Larkspur's Q1 cost report has no error codes but the total is wrong twice over. You remove the double-counted subtotals, find the numbers stored as text, measure what they leave out and reconcile the report with the ledger.
• Build a check panel for Larkspur's regional sales model: a cross-foot difference, OK/CHECK flags with a tolerance, the repair of a stale hard-coded total and a red highlight that keeps the open issue visible.