Excel© Tips and Tricks

Trapping Error Messages by Using the IF Error Function

If you’ve worked with formulas, you’ve probably encountered the dreaded formula error; rather than returning a numeric result, the formula cell displays a strange message such as #VALUE! or #DIV/0!.

Most of the time, 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. For example, say you run a small telemarketing company. You might have a spreadsheet set up to track your daily sales as a percentage of calls made.

Applies to: MS Excel 2003, 2007 and 2010

v2

1. The formulas in column D do the calculations that come up with the percentages. For example, cell D4 contains the formula =C4/B4.The answer was then converted to percentages by using the percent style option.

2. The formula does its job well–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, Excel displays an ugly #DIV/0! error message, which makes your entire worksheet look like it was created by a novice.

3. You can avoid displaying formula errors by rewriting your formula to use an IFError function. For example:

=IFERROR((C4/B4),” »)

4. Displays a blank cell if the division operation results in an error (cell B4 is empty or contains 0), yet still displays valid results.

5. 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.

Leave a reply

Pour oublier votre commentaire, ouvrez une session par l’un des moyens suivants :

Logo WordPress.com

Vous commentez à l’aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l’aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l’aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l’aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s