Skip to content

Cellwise Try it free

Unit 8 of 12 Lesson 1 free · Plus

Clean results

Handle errors and round like a pro.

Functions in this unit: IFERROR, VLOOKUP, ROUND, ROUNDUP, AVERAGE, IF, SUMIF, XLOOKUP, AVERAGEIF, TEXT

Key ideas

Know your errors

#DIV/0! #N/A #NAME?

Each error tells you what went wrong. Read it before hiding it.

IFERROR gives a fallback

=IFERROR(B2/C2,0)

If the formula fails, show the fallback instead of an error.

ROUND changes the value

=ROUND(B2*C2,2)

Formatting only hides decimals. ROUND actually changes the number so totals match.

Lesson 1: Reading errors

Match each error to its cause
#DIV/0!
Dividing by zero
#N/A
Lookup value not found
#NAME?
Misspelled function
#VALUE!
Wrong type of data
What will C1 show?
A B C
1 50 0
C1 contains =A1/B1
Show answer
#DIV/0!. Excel cannot divide by zero.
Price per unit is Revenue divided by Units. Some units are 0. Show 0 instead of an error. Start in D2, fill to D5.
A B C D
1 Product Revenue Units Per unit
2 Notebook 420 120
3 Lamp 0 0
4 Pen 425 340
5 Easel 0 0
Hint: =IFERROR(B2/C2,0)
Show answer
=IFERROR(B2/C2,0) in D2. IFERROR catches the #DIV/0! on the zero rows.
What does #NAME? usually mean?
- A function or name is misspelled
- A cell is empty
- A number is too large
- A lookup failed
Show answer
A function or name is misspelled. Check spelling first: =SUMM(A1:A3) gives #NAME?.
Look up the price for F2, but show "Not found" instead of #N/A. Put it in G2.
A B C D E F G
1 ID Item Price Stock Find ID Price
2 P-101 Desk lamp 34.99 12 P-999
3 P-102 Monitor arm 89 4
4 P-103 USB hub 24.5 30
5 P-104 Webcam 59.95 0
6 P-105 Keyboard 74 18
7 P-106 Mouse pad 12.75 52
Hint: Wrap the VLOOKUP inside IFERROR.
Show answer
=IFERROR(VLOOKUP(F2,A2:D7,3,FALSE),"Not found") in G2. A friendly message beats a scary error for the people reading your sheet.

Lesson 2: Rounding

Lesson 2 is part of Cellwise Plus. Lesson 1 above is free to read here and to practice in the app.

See what Plus includes

Lesson 3: Final challenge

Lesson 3 is part of Cellwise Plus. Lesson 1 above is free to read here and to practice in the app.

See what Plus includes

Guides on this topic