In last week’s tip we had a look at some common Excel errors such as #VALUE! or #DIV/0! and how to correct them.
If you work with formulas, you’ve probably encountered a formula error; this means you need to track down the source of the error and fix it. But sometimes a formula error simply means that the data used by the formula is not yet available.
Applies To: Microsoft® Excel® 2010 and 2013
Let’s take a look at an example in the sales industry. It might be a requirement of you in telemarketing to track your daily sales as a percentage of calls made.
- The formulas set up in column D would bring up the numeric result that you see. Cell D4 contains the formula =C4/B4.
The answer was then converted to percentages by using the percent style option. - The formula works as long as there is data to calculate. An empty cell (such as B9) is treated as a zero, and division by zero is not allowed. As a result, the #DIV/0! error message is displayed which can make your worksheet look cluttered.
- You can avoid displaying formula errors by re-writing your formula to use an IFERROR function. For example:
=IFERROR((C9/B9);””)
- This will display a blank cell if the division operation results in an error (cell B9 is empty or contains 0), yet still displays valid results
- If you prefer, you can replace the empty string (“”) with other text of your choice–just make sure the text is enclosed in quote marks.
=IFERROR((C9/B9), “Please check your formula”)
