IFERROR and IFNA practice exercises
#DIV/0!, #N/A and #VALUE! make reports look broken. Learn when each error appears and how to replace it with a clean result using IFERROR, IFNA and ISBLANK.
Try it yourself
A live spreadsheet. Type a formula and press Enter to check it.
Work out the cost per unit for Travel (Actual ÷ Units). Units is 0, so show 0 instead of an error.
Write your formula in the green cell, G2.
Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.
All 11 error handling exercises
- 1→
Avoid #DIV/0!
FreeWork out the cost per unit for Travel (Actual ÷ Units). Units is 0, so show 0 instead of an error.
- 2→
Missing lookups
FreeLook up the budget for Events. It isn't in the table, so return "No budget".
- 3→
Check for a number
PremiumIs there a number in Training's Actual cell? Return TRUE or FALSE.
- 4→
Count empty cells
PremiumHow many categories have no Actual figure entered yet?
- 5→
Budget variance
PremiumBy what percentage did Marketing go over budget? (Actual − Budget) ÷ Budget.
- 6→
IFERROR with a message
PremiumWork out the cost per unit for Supplies (Actual ÷ Units). It has no units, so show "No units" instead of an error.
- 7→
Blank or not
PremiumReturn "Not started" if Training's Actual cell is blank, otherwise "Started".
- 8→
Check before you calculate
PremiumWork out Training's variance %, (Actual − Budget) ÷ Budget, but show "No data" if its Actual cell is blank.
- 9→
IFNA with MATCH
PremiumFind the position of "Hardware" in the Category list. It isn't there, so return "Missing" instead of #N/A.
- 10→
How many went over?
PremiumHow many categories spent more than their budget? Use one formula.
- 11→
Add up with a condition
PremiumWhat is the total overspend: how much the categories that went over budget spent above their budgets, added together?
The error handling functions, with examples
Every example runs on team-budget.xlsx, the same sheet the exercises use, and the results are calculated from that data.
IFERROR
Returns a value of your choice when a formula gives any error, and the normal result otherwise. Here, Travel has no units, so the division fails.
IFERROR(value, value_if_error)Example
=IFERROR(C4/D4, "No units")returnsNo unitsIFNA
Like IFERROR, but only catches #N/A, the “not found” error, so other mistakes still show.
IFNA(value, value_if_na)Example
=IFNA(VLOOKUP("Legal", A2:B7, 2, FALSE), "Not budgeted")returnsNot budgetedISBLANK
Returns TRUE if a cell is completely empty. Useful for flagging missing data before it breaks a calculation.
ISBLANK(value)Example
=ISBLANK(C6)returnsTRUESUMPRODUCT
Multiplies ranges row by row and adds up the results. Conditions written as (range>value) turn it into a flexible conditional sum.
SUMPRODUCT(array1, [array2], …)Example
=SUMPRODUCT((C2:C7>B2:B7)*(C2:C7-B2:B7))returns$355.00
Common mistakes
- 1
Hiding every error with IFERROR
IFERROR(…,0)also hides typos and broken references, so a wrong total looks fine. Catch only the error you expect: IFNA for lookups, orIF(D2=0,…)for a divisor that can be zero. - 2
Assuming empty means zero, or "" means empty
Empty cells count as 0 in arithmetic, and ISBLANK is FALSE for a cell whose formula returns "". When formulas fill a column, test with
C6="", which is TRUE in both cases. - 3
Treating every error the same
Each error points somewhere different: #N/A means a value wasn't found, #VALUE! means the wrong type (like text in a sum), #DIV/0! means dividing by zero or an empty cell, and #REF! means the formula points at cells that were deleted.