It is common to produce errors in excel while working. Sometimes errors are even intentional. But how can we trap expected errors in excel?
There are four functions that can be used to trap basic errors in excel. These functions are:
These four functions are used to trap errors in excel differently. In this article, we will learn how to use these functions to trap expected errors in Excel.
The ISERROR function also traps errors but unlike IFERROR function, this function returns TRUE/FALSE. If the expression wrapped in an ISERROR function results in an error, the ISERROR functions will return TRUE, if not, it will return FALSE.
| =ISERROR(expression) |
So, for example, we have this expression:
| =ISERROR(VLOOKUP("Ralph",A2:D10,2,0)) |
If "Ralph" is found in the table, it will return FALSE as there are no error, if VLOOKUP does not finds it , ISERROR will return TRUE as there is an #N/A error.
Basically, most of the time ISERROR is not used to trap error, as the IFERROR does it better. But ISERROR is used where we want to convert some results into TRUE and FALSE.
For example, sometimes we want to create an array of TRUE false to do some complex lookups. In such cases, you might not need the result of expression but only if it returns an error or not. In such cases, we use the ISERROR function.
See this.
| =ISERROR(1/A2:A10) |
Now this formula will return an array of true and false. Wherever in range A2:A10 value is 0, we will get an #DIV/0 error. The ISERROR function checks all the values and returns TRUE for an error and FALSE for a non error.
The below formula is used to find the first error in a range.
| =MATCH(TRUE,ISERROR(1/A2:A10),0) |
This is one of the most used functions to trap errors in excel and reasonably so. The IFERROR function traps any kind of error and returns a customised output if an error is found. The basic syntax of this function is.
| =IFERROR(expression,value_if_error) |
This is basically a combination of IF and ISERROR function.
This function is used to encapsulate the expression that may return error. And if an error occurs you can define some other expression.
For example, if you are looking up for a value using VLOOKUP function in some data set and you know that you may not find some value. In such cases, instead of #N/A error, you want something meaningful than you can use this function.
| =IFERROR(VLOOKUP("Ralph",A2:D10,2,0),"Ralph is not in dataset1") |
Here the VLOOKUP function will look for string Ralph in dataset1 (A2:D10). If it finds the ralph, it will return the value, if it does not find the value, it will return the string "Ralph is not in dataset1" instead of #N/A error.
The IF and ISNA alternative of this formula will be,
| =IF(ISERROR(VLOOKUP("Ralph",A2:D10,2,0)),"Ralph is not in dataset1",VLOOKUP("Ralph",A2:D10,2,0)) |
You can even replace it with another function. For example, if VLOOKUP does not find value in 1 table, lookup in some other table. I am not explaining it here as I have a separate article on it and I don't want to make it too lengthy. Read this Use IFERROR to lookup from Two or more Tables.
The ISERR function is the same error as the ISERROR function. The only difference is this that it does not consider #N/A in its calculations. In other words, ISERR function traps all the errors except #N/A error. Why?
Syntax of ISERR
| =ISERR(Expression) |
There will be times when you will want to know why #N/A error occurs and want to trap all other errors. In such cases, ISERR will return TRUE if it finds any error other than #N/A error. You will be able to see the #N/A error.
For example, in a lookup formula, we are certain that value exist in the lookup range and it should not return #N/A error but we want to trap other errors. In such cases we use ISERR function.
| =ISERR(VLOOKUP("Ralph",A2:D10,2,0)) |
Now if the formula returns #N/A error, it means you might need to check data or the formula.
If you want to know what types of error it traps, here's the list.
Where the ISERR function traps all the errors except the #NA error, the ISNA function traps only #N/A error.
The syntax is of the ISNA function.
| =ISNA(Expression) |
This function will trap only #N/A error.
So if I have this expression,
| =ISNA(VLOOKUP("Ralph",A2:D10,2,0)) |
This formula will return TRUE only if the VLOOKUP function returns #N/A error. Otherwise it will return FALSE.
This can be useful to do something only if #N/A error occurs.
It is similar to the IFERROR function. The IFNA function returns a customised answer if it traps an #N/A error, else it will return the value returned by the expression inside it.
The syntax of this function is:
| =IFNA(expression,value_if_error) |
So if we have this expression below:
| =IFNA(VLOOKUP("Ralph",A2:D10,2,0),"Ralph is not in dataset1") |
If the VLOOKUP finds the value in the given table, this expression will return the value. If this VLOOKUP expression returns a #N/A error, this formula will return "Ralph is not in dataset1". But if VLOOKUP results in an error other than a #N/A error, the IFNA will return that error.
This function is basically a combination of IF and ISNA functions.
The equivalent formula of the above formula will be,
| =IF(ISNA(VLOOKUP("Ralph",A2:D10,2,0)),"Ralph is not in dataset1",VLOOKUP("Ralph",A2:D10,2,0)) |
This is an amazing function. If the expression encapsulated in this function returns an error, this function will return a numeric value, ranging from 1 to 14, each representing a different type of error. If the expression does not returns any error, this function will return #N/A error. Funny!
The syntax of this function is,
| =ERROR.TYPE(expression) |
Check this formula:
| =ERROR.TYPE(1/0) |
This formula will return 2. Why? We know that 1/0 will return #DIV/0 error. The error code for #DIV/0 error is 2. Hence this statement will return 2.
So, if you want to trap #DIV/0 error specifically, you can use this function along with IF.
For example, see the below expression:
| =IF(ERROR.TYPE(A2/B2)=2,"You can't divide by 0", A2/B2) |
The above formula will write "You can't divide by 0" in the cell if B2 is 0, else it will return value of A2/B2.
You can use CHOOSE or SWITCH function to trap each type of error.
The errors and codes are:
Error Code
#NULL Error - 1
#VALUE Error -3
#REF Error -4
#NAME Error -5
#NUM Error -6
#N/A Error -7
#Getting_Data -8
#SPILL Error -9
#UNKNOWN! -12
#FIELD Error -13
#CALC! Error -14
Note that there are no 10 and 11 codes. After 9 it skips to 12.
So yeah guys, these are the functions that you can use to trap almost all kinds of errors. But this doesn't mean that you won't get any error. But it will be easy to deal with them now. There is one error that you can't trap and that is a logical error. Here you need to be most careful.
Hope this article about How to Avoid Error Displays when Formulas Return Result Calculations is explanatory. Find more articles on different types of error and how to resolve those errors here. If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook. We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write to us at info@exceltip.com.
Related Articles:
Formula Errors in Excel and Solutions : While trying to do something with a formula, you will encounter various types of excel errors. These errors can make your formula/dashboard/reports absolute waste.
How to Trace and Fix Formula Errors in Excel : It means if you can solve that error, all of the errors will be solved. To trace the errors, excel provides an error tracing tool. It is in the formula tab of the ribbon, in the formula auditing group, under Error Checking.
Maximum value from the list having errors : At Instance, we have a list of numbers having some error values in a list. We need to find the MAX number from the list. How can we obtain such values? Excel MAX function generates #NA error, if used.
Why ##### (hashtag) occur and How to fix ###### (hashtag) error :In excel, at times we get #### (hashtag) error. This error occurs when there is nothing missing in the cell, it's just that there isn't enough space for the new or updated value. As per Microsoft official site a #### (hashtag) is Excel's way of saying, the cell
F9 Key to Debug Excel Formulas :To solve formula errors or to evaluate how a complex formula in excel working, one must know how to debug/evaluate excel formulas. In this article, we will learn how to debug or evaluate an excel formula.
Evaluation of Excel Formula Step By Step : Excel provides functionality that helps us evaluate the formula. We can see how a formula is working by stepping through the formula. In this article, we will learn how to evaluate the formula option of Excel.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use Excel VLOOKUP Function| This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the Excel COUNTIF Function| Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
"Why bother with this when you can just use an If Statement...
=IF(A2=0,0,A1/A2) Surely, there must be a better way?"