IFERROR returning 0 so totals still add
A text fallback turns a number column into a mixed one; a zero keeps it numeric.
Task:
Cost per unit is cost divided by units, and two product lines made nothing this month. In D2:D6 give the cost per unit, returning 0 where there were no units, and in D7 the total of the column.
Interactive Spreadsheet
Hints
+5 XPno-hints bonusSolve it on your own to keep the bonus. Each hint gets one step closer to the formula.
Variations of IFERROR
0/3The same formula in the other shapes it takes at work.
- IFERROR Function
Handle errors gracefully using IFERROR.
CoreIntermediate - IFERROR returning 0 so totals still add
A text fallback turns a number column into a mixed one; a zero keeps it numeric.
Intermediate - IFERROR around a conversion
Turn imported text into numbers, and anything unreadable into a known default.
Intermediate
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Line | Cost | Units | Per unit |
| 2 | Mugs | 840 | 420 | |
| 3 | Plates | 0 | 0 | |
| 4 | Bowls | 615 | 205 | |
| 5 | Jugs | 120 | 0 | |
| 6 | Trays | 450 | 150 | |
| 7 | Total |
What this exercise teachesMay contain the answer
Returning "N/A" hides the error from a reader but not from SUM, which ignores text — fine here — yet averages, charts and lookups downstream often do not. Returning 0 keeps the column numeric. Note the trade-off: Jugs had a real cost and no units, and 0 hides that, so use this where 0 genuinely means "nothing to report".