Avoid #DIV/0!

IntermediateSkip →
Exercise 1 of 11IFERROR

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.

Error handling

Avoid #DIV/0!Missing lookupsCheck for a numberCount empty cellsBudget varianceIFERROR with a messageBlank or notCheck before you calculateIFNA with MATCHHow many went over?Add up with a condition
team-budget.xlsx
fx
1CategoryBudgetActualUnits
2Rent$2,400.00$2,400.001Travel/unit
3Software$600.00$645.0012
4Travel$1,500.00$0.000
5Marketing$2,000.00$2,310.005
6Training$800.000
7Supplies$300.00$276.000
8
9
10

Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.

About error handling in Excel

#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.

Functions you can use here

  • IFERROR(value, value_if_error)
  • IFNA(value, value_if_na)
  • ISBLANK(value)
  • SUMPRODUCT(array1, [array2], …)

More error handling exercises

  1. 1.Avoid #DIV/0!
  2. 2.Missing lookups
  3. 3.Check for a numberPremium
  4. 4.Count empty cellsPremium
  5. 5.Budget variancePremium
  6. 6.IFERROR with a messagePremium
  7. 7.Blank or notPremium
  8. 8.Check before you calculatePremium
  9. 9.IFNA with MATCHPremium
  10. 10.How many went over?Premium
  11. 11.Add up with a conditionPremium
See the whole topic →