Excel track · Exercise 61 of 86 Paid
Formula audit: fix five broken sheets
Repair five broken cells, one per error code, by choosing the test each code implies.
Advanced
Five broken sheets, one per error code, to repair in place
Read-only data · screen 1 of 5 · answer in D3
In D3, repair the ratio for the rep with no visits so it reads n/a instead of an error.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Visits | Orders | Ratio |
| 2 | Ana | 10 | 4 | |
| 3 | Bo | 0 | 0 | |
| 4 | Cy | 8 | 6 | |
| 5 | Di | 5 | 0 |
In F2, repair the lookup so a name that is not in the list reads not found instead of an error.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Visits | Orders | Ratio | Lookup | |
| 2 | Ana | 10 | 4 | Zed | ||
| 3 | Bo | 0 | 0 | |||
| 4 | Cy | 8 | 6 | |||
| 5 | Di | 5 | 0 |
In D3, repair the line value so a quantity that is not a number contributes nothing instead of failing.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Qty | Price | Value |
| 2 | bolt | 4 | 2.5 | |
| 3 | nut | n/a | 1.5 | |
| 4 | washer | 10 | 0.4 | |
| 5 | screw | 6 | 0.8 |
In D2, repair the North total so its ranges cover every row of the table.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Amount | Answer |
| 2 | North | 100 | |
| 3 | South | 200 | |
| 4 | North | 150 | |
| 5 | East | 90 |
In D2, repair the average so it stops reading a name error and reports the score column.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Score | Answer |
| 2 | Ana | 82 | |
| 3 | Bo | 91 | |
| 4 | Cy | 78 | |
| 5 | Di | 88 |
Reference answer
What it does
Each error code names one kind of break. A division error means the denominator can be zero, so the repair is a test in front of the division. A not-available error means a lookup found no row, so the repair tests for the key before asking for it.
A value error means an operand is text where a number belongs, which ISNUMBER catches. A reference error means a range points outside the table, and a name error means the function was typed wrong. Both are read off the sheet, and neither needs a guess about the arithmetic.
The five screens take one code each, and each repair uses a different function: IF, COUNTIF, ISNUMBER, SUMIF and AVERAGE. Working from the code down stops you rewriting a formula that was one argument short of right.
Syntax
each error code names one break: a zero divisor, a missing key, text in arithmetic, a wrong range, a misspellingWorked example
On Five broken sheets, one per error code, to repair in place, =IF(B3=0,"n/a",C3/B3) returns n/a.
=IF(B3=0,"n/a",C3/B3)Common errors
| Error | What it means and how to fix it |
|---|---|
#DIV/0! | A denominator is zero, so the division has no result and the cell needs a test in front of it. |
#N/A | A lookup key is not in the table, which leaves no row to read, so the key has to be counted first. |
#VALUE! | An operand is text where a number belongs, so the arithmetic needs a type test before it runs. |
At work
Opening a workbook someone else built and repairing each cell from the error code it shows.