Formula Errors in Excel? Don’t fix them, Ignore them. Microsoft Excel has millions of cells that allow any value to be entered: Numbers, Text, Formulas. In fact the default setting in the Data Validation dialog box is set to “Allow Any Value”. This default setting can allow formulas to receive incorrect or unexpected values thus forcing the formula to return errors such as #DIV/0.
Perhaps the easiest way to deal with crabby formulas that work only when they get what they want, is to use the IFERROR function found in the newer versions of Excel (2007 or newer).
Our formulas may be correct but the cells feeding a formula may contain a blank or a zero. Sometimes this cannot be avoided. So what do we do? Scan the whole spreadsheet looking for errors such as DIV/0? No. Nip the problem in the bud.
Write the formula as you always have then wrap it in the IFERROR function.
For instance, instead of writing =B5/C5 try writing =IFERROR(B5/C5,0) or =IFERROR(B5/C5,””) or =IFERROR(B5/C5,”N/A”). This can be used on any formula that could possibly return an error due to incomplete data or zeros.
Your spreadsheets will look great even if the data is not perfect.