 |
|
|
|
|
Without doubt, as you write formulas you will at times generate an error as a result, instead of your expected result. Knowing what the error means will go a long way to helping you identify the problem.
The type of errors you can expect are:
#DIV/0!
#N/A
#VALUE!
#REF!
#NUM!
#NULL
#NAME?
Lets look at each of these in turn and see what they are trying to tell us!
This one is nice and simple, it tells us we are trying to divide a number by zero. This is a no no in math. You would get this if you tried to divide a number by an empty cell, as well as a cell containing zero. This is because an empty cell has a value of zero.
This is a very common error in Lookup formulas, eg; VLOOKUP, HLOOKUP etc. It is telling us that no match can be found. You will also get this error if the list or table you are looking in contains #N/A.
This one will occur if you have used the wrong type of argument in a Formula. If cell C4 contained a text entry then using =2+C4 would result in a #VALUE! error. This is because Excel was expecting an number and not text.
This one will occur when or if a cell reference in not (or no longer) valid. Let's say you have a simple formula like: =A10 in any cell. You then deleted the cell (not the content) you would end up with the #REF! error.
This one is not so common and is similar to the #VALUE! error. In other words you have used the wrong type of argument for a formula.
The #NULL error will occur when you have used the intersection of two areas that do not intersect. For example: =SUM(A1:B10 D10:D10) Would result in #NULL as we have forgotten the comma, which is used as our union operator.
This will occur when Excel does not recognise the text in a formula. In most case it is telling you that you have misspelt the name of a function.
At times we may expect to receive errors in our functions, but we do not want to view them as they are not very pleasing to the eye and on top of that they can cause errors in other cells that may have them included in their reference.
We can suppress the errors returned by a formula by using one of Excels Information functions. These functions are generally used to determine the content of a cell or range of cells BEFORE performing a particular function. We would nest our formula within one of the Information functions.
Lets say we know that cell A1 could at times be empty or have a zero in it, so we want to be able to suppress the #DIV/0! error we would get if we try to divide cell A1 into another cell or number.
We could use:
=IF(ISERR(20/A1),"",20/A1)
the ISERR will return TRUE for any error type, except #N/A. To return TRUE for any error type we could use:
=IF(ISERROR(20/A1),"",20/A1)
Making use of the error type functions is certainly not the only way we can prevent errors. For instance for this particular case we can also use:
=IF(AND(ISNUMBER(A1),A1<>0),20/A1,"")
Here we have used the ISNUMBER function together with <> (the "not equal to" sign) nested within the AND function, which in turn is nested within the IF function. In all examples we have replaced any possible errors with "" (Empty text). If your formula was one of the Lookup and Reference type you should only suppress the #N/A! error. You would do this by nesting your lookup formula with the ISNA error function. Remember through, if you suppressed all errors you may never know if you have a problem
A word of warning when suppressing errors. Leave it until the end, otherwise you may spend a lot of time trying to track down any other errors that you are not expecting. When you do finally suppress errors (or possible errors) try and limit it to what you expect, don't be tempted to take a blanket approach and suppress all errors. Remember the error is trying to tell you something, so use them to your advantage.