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