When you use a formula to calculate a new column of numbers, Excel will sometimes display error messages such as #DIV/0! if a number has been divided by zero.
Use the ISERROR function to eliminate error messages and keep the column of data tidy
Worked example:
- Start up Excel. Open a blank worksheet.
- In cells A1 to A6, type: 10, 20, 30, 40, 50, 60 (no commas)
- In cells B1 to B6 type: 1, 2, 3, 4, 5, 6
- Now in B2 and B4, replace the 2 and the 4 with 0 (zero).
- In C1, divide B1 into A1 with the formula = A1/B1. You should see 10 in C1
- Now copy down the formula to C6. You get the #DIV/0! error message in B2 and B4.
Insert ISERROR to avoid this
- Click onto cell C1.
- Either double left click on it, or press the F2 key. This allows you to edit the cell.
- Amend.... = A1/B1 to... =IF(ISERROR(A1/B1),"-", A1/B1)This means: "If A1/B1 turns out to be an error, print a dash, otherwise print A1/B1"
- Copy the new formula down to C6. The #DIV/0! errors are replaced with dashes. The dashes are left justified (the quotation marks in "-" are telling Excel that this is text). Move them to the right by clicking on the Align Right icon.
Contributed by David Carter for our sister site, AccountingWEB.co.u
