Question: Excel is known for its excellent formulae & 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 formulae 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
