Business Automation and Software Blog

Excel Tips and Tricks: Error Trapping

Posted by Robert Baran on Fri, Jun 29, 2012 @ 12:27 PM

Excel Tips and Tricks / Business Reporting Question: Excel is known for its excellent formula & function capabilities. As such I extensively use Excel to calculate the variance between the target sales amount and the actual sales amount for our salesagents. However, sometimes the formula returns errors/error messages such as #DIV/0! Is there a way of trapping error messages, so that a custom message is displayed instead of an error message?

Answer: Yes, with the IFError function

Why: In order to trap error messages so that a custom message is displayed as opposed to an error message

Applies To (Excel 2007 and 2010)

1. Create the spreadsheet as in the example below:

Excel Tips and Tricks

2. Select cell G5 and type =-(E5-F5)/E5. Press Enter and auto-fill the formula down.

3. The screen shot below with #DIV/0! Error messages will be displayed. The variance figures have been converted to percentage.

Excel Tips and Tricks

4. To rectify the errors above; select cell G5 and type: =IFERROR(-(E5-F5)/E5,"Target not given")

5. The screen shot below will be displayed. As you can see the variance has no error messages displayed. Instead a custom error message is given which gives credibility to the data.

Excel Tips and TricksContact PositiveVision ► ► for a Complimentary  Reporting Consultation

Topics: Business Reporting, Excel Tips and Tricks