It can happen to the best of us. You have a nice spreadsheet designed. Then some numbers change and in creeps an error.
The exercise and result are in the Error file.
Here we have a worksheet where we calculate average sales.
This sheet worked fine, until one rep took a leave. Sales and units dropped to zero.
You may already know one fix, by checking for zero.
The formula is =IF(C4>0,ROUND(B4/C4,2),0). The test is to see if the value is greater than zero.
Here is another way, using one of the error trapping functions of Excel.
The alternate formula is =IF(ISERROR(B4/C4),0,ROUND(B4/C4,2)).
The syntax is to put the formula or the cell referred to in the parenthesis. If there is an error, the test is true. That is why we had to flip the true and false actions in the IF statement.
In addition to ISERROR, there is also a very similar one called ISERR.
ISERROR- becomes true on any error value- Value refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!).
ISERR- becomes true on any error value except #N/A.
There is also another error trapping function called ISNA, which we covered in our lookup lessons. #N/A is a common error that is generated with lookups. ISNA only traps #N/A errors.
In our example above, the first solution, the divide be zero test is fine. However, if you think there could be other errors that could come up that you want to trap, then you might be better off using the ISERROR function in your test, which we did in the second solution. On the other hand, in this case, you might say, the only error you want to trap is the #DIV/0! error and if any other errors occur, you want to know about them and fix them. If so, use the first solution.
The more potential errors you can anticipate ahead of time, the better. Anytime you divide, there can be an error if you have a risk of dividing by zero, for example. This gives you a couple different ways to fix the problem.