Course / Logic & Decisions

IFERROR and IFNA

Replace #DIV/0! and #N/A with friendly results that keep a report readable. Intermediate · 4 min · IFERROR, IFNA, #DIV/0!, #N/A

Errors like #DIV/0! and #N/A are Excel's way of saying a formula couldn't produce an answer. They're useful while you build a sheet, but they look alarming in a finished report. They also spread: any formula that uses an error cell shows the error too.

• =IFERROR(formula, fallback) returns the formula's result, or the fallback if it gives any error
• =IFNA(formula, fallback) does the same, but only for #N/A, the "not found" error from lookups

The fallback can be text, a number, or empty text "".

Steps

1. The wool scarf sold nothing this week, so D5 divides by zero. Edit D2 to =IFERROR(B2/C2, "No sales"), then copy it down to D7.

2. Column E looks up each product's maker in the list at G2:H6 using VLOOKUP, which you'll meet in the Lookups track. Two products aren't on the list yet, so they show #N/A. Wrap the formulas in E2:E7 with IFNA to show Not listed instead.

3. In D9, find the average price across the products in D2:D7.

Remember

• =IFERROR(formula, fallback) replaces any error.
• =IFNA(formula, fallback) replaces only #N/A, so genuine mistakes still show.
• Wrap the original formula rather than replacing it.
• Pick the fallback with care: SUM and AVERAGE skip text, but they count 0.

Previous: AND, OR and NOT · Next: COUNTIF