The Iserror formula

Definition

The “ISERROR” function is very handy when doing reporting. This basically takes an answer which would have been an error and gives another specified value. Errors can look like follows:

So instead of getting a #div/0! you can just give a zero value. You would also use the “ISERROR” with the “IF” function

Spaces in excel

Another interesting thing to note with errors, is that when excel sees a space that someone actually made, like one space (“ ”) or two spaces (“  ”). When no person actually went to the cell and clicked on the space to create a space, the cell is empty. Being empty and having a space is two different things. Excel will add up empty cells but not cells with spaces in.

Examples

Example

Let’s say we are trying to divide a1 by b1 (=a1/b1). The value in a1 is 4 and the value in b1 is 0. The answer would be #div/0!

We can correct the answer with the Iserror function to show another value:
=if(Iserror(a1/b1),”Please insert value in column B”,a1/b1)

We basically put an Iserror function in the logical test field in the “if” function. We say, if the formula (a1/b1) is an error. (This is our logical test). The next field of an “if” function is the true field. So what must be done if our logical test is true? In other words, if (a1/b1) is an error, what must be done, put the words “please insert value in column B”, The next field is the false field. So what must be done if the formula (a1/b1) is not an error, that means that a value exists in column B. then we must do the formula. So if there is not an error then the original formula must be done.

Example

If we do a Vlookup formula, and our value does not exist a #n/a would be given, but we still want to able to sum our returned values.

Let’s say we have the names of our top 100 employees, but we want to know their salaries. We obtained a file from HR with all the employees’ salaries. The names are the same on both data files, so we can do a “Vlookup” to get the salaries. But some of these top 100 resigned and is not on there anymore.

For examples sake only five names are used for our example:

We searched for the values, but Koos is not working with us anymore, and because this gives us an error, we cannot add up our salaries to get a total.

To fix this we can do an iserror formula like follows:

It looks difficult, but is actually easy.

First we say what our logical test is, to test if our Vlookup formula would give an error or not. Putting the brackets at the correct place is very important now; remember to close the Vlookup formula and the Iserror, before doing the next field which is the true field.. So what must happen if the Vlookup formula gives an error? Put a 0 in the field If there is no error then the formula is false and then the Vlookup formula must be done as normally. Also note the two brackets at the end. One is to close our original if formula and one is to close our last Vlookup formula. Remember to click on the function “fx” at the top in excel to start the formula easier. You don’t have to do the same formula again in the false field, you can also like a normal if formula put another value in there. For example we can just say “Worker does not work here anymore”, if the result is an error instead of giving a zero . In the false field we can say “worker available” if the result does not give an error . Our formula will look like follows:

=IF(ISERROR(VLOOKUP(A2,Sheet2!$A:$B,2,FALSE)),"Does not work here anymore","worker available")

Also remember that if the workers name is not spelled the same in both files, then the formula will be an error. Believe me; using an Iserror function makes life much easier. It makes reports quicker and it looks better. Instead of deleting all the errors, it can be gone from the start.

Things you can do with the iserror formula

• Take out all errors and show something more readible
• Perform another formula if an error occurs

<-----Back to index

Next topic----->