Excel Tip Error Trapping

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