Unit 8 of 12

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.

	A	B	C
1	50	0

	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

	A	B	C	D	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

Lesson 2: Rounding

Calculate revenue rounded to 2 decimals in E2. Fill to E7.
A B C D E
1 Product Units Price Discount Revenue
2 Notebook 12 3.333 0.1
3 Pen 7 1.255 0.1
4 Stapler 3 12.499 0.1
5 Binder 9 6.745 0.1
6 Marker 5 2.4049 0.1
7 Folder 11 1.901 0.1
Hint: Revenue after discount is B2*C2*(1-D2). Wrap it: =ROUND( ... ,2)
Show answer
=ROUND(B2*C2*(1-D2),2) in E2. Rounding inside the formula keeps your totals matching the receipts.
What will A1 show?
A
1
A1 contains =ROUND(3.456,1)
Show answer
3.5. The digit after the cut is 5, so it rounds up.
What will A1 show?
A
1
A1 contains =ROUNDUP(2.1,0)
Show answer
3. ROUNDUP always goes away from zero.
What is the difference between formatting to 2 decimals and ROUND(x,2)?
- Formatting only changes what you see, ROUND changes the value
- They are the same
- ROUND only changes what you see
- Formatting is more accurate
Show answer
Formatting only changes what you see, ROUND changes the value. Hidden decimals still add up, which is why totals sometimes look off by a cent.
Average Test 1 scores rounded to a whole number. Put it in B8.
A B C D
1 Student Test 1 Test 2 Test 3
2 Maya 92 88 95
3 Leo 67 74 70
4 Priya 85 91 78
5 Sam 58 62 71
6 Nora 79 83 88
7 Eli 96 90 99
8 Average
Hint: =ROUND(AVERAGE(B2:B7),0)
Show answer
=ROUND(AVERAGE(B2:B7),0) in B8. Zero decimals means a whole number.

	A	B	C	D	E
1	Product	Units	Price	Discount	Revenue
2	Notebook	12	3.333	0.1
3	Pen	7	1.255	0.1
4	Stapler	3	12.499	0.1
5	Binder	9	6.745	0.1
6	Marker	5	2.4049	0.1
7	Folder	11	1.901	0.1

	A
1

	A
1

	A	B	C	D
1	Student	Test 1	Test 2	Test 3
2	Maya	92	88	95
3	Leo	67	74	70
4	Priya	85	91	78
5	Sam	58	62	71
6	Nora	79	83	88
7	Eli	96	90	99
8	Average

Lesson 3: Final challenge

Sales of $1,000 or more earn 5% commission, rounded to cents. Otherwise 0. Start in C2, fill to C6.
A B C
1 Rep Sales Commission
2 Ava 1250.5
3 Ben 980
4 Cara 2033.33
5 Dev 1000
6 Eva 615.2
Hint: =IF(B2>=1000, ROUND(B2*0.05,2), 0)
Show answer
=IF(B2>=1000,ROUND(B2*0.05,2),0) in C2. IF decides, ROUND cleans. Dev at exactly 1000 qualifies.
Total revenue for the North region. Revenue is in column E. Put it in H2.
A B C D E F G H
1 Product Region Units Price Revenue North revenue
2 Notebook East 120 3.5 420
3 Pen West 340 1.25 425
4 Stapler East 45 12 540
5 Binder North 80 6.75 540
6 Marker West 210 2.4 504
7 Folder North 150 1.9 285
Hint: Check column B for "North", add column E.
Show answer
=SUMIF(B2:B7,"North",E2:E7) in H2. SUMIF works on formula results just like typed numbers.
Look up the stock for the item named in F2. Show "Unknown" if it is missing. Put it in G2.
A B C D E F G
1 ID Item Price Stock Find item Stock
2 P-101 Desk lamp 34.99 12 Webcam
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: Search B, return D, with "Unknown" as the 4th argument.
Show answer
=XLOOKUP(F2,B2:B7,D2:D7,"Unknown") in G2. Stock is 0, which is a real answer, not an error.
Build: average score of Ops, rounded to 1 decimal
Show answer
=ROUND(AVERAGEIF(B2:B7,"Ops",C2:C7),1). Inner function first, then wrap with ROUND.
What will D1 show?
A B C D
1 Pen 4 1.25
D1 contains =IF(B1*C1>=5,"Free shipping","Add "&TEXT(5-B1*C1,"$0.00"))
Show answer
Free shipping. 4 times 1.25 is exactly 5, which meets the >= test.

	A	B	C
1	Rep	Sales	Commission
2	Ava	1250.5
3	Ben	980
4	Cara	2033.33
5	Dev	1000
6	Eva	615.2

	A	B	C	D	E	H
1	Product	Region	Units	Price	Revenue	North revenue
2	Notebook	East	120	3.5	420
3	Pen	West	340	1.25	425
4	Stapler	East	45	12	540
5	Binder	North	80	6.75	540
6	Marker	West	210	2.4	504
7	Folder	North	150	1.9	285