At one point or another, we've all had to spend valuable time clicking between various Excel sheets to find a hidden error. These can be hard to spot and errors such as #DIV/0! can cause your entire worksheet to stop working. On the bright side, there are a number of ways you can check for and manage errors using Excel.
One way to do this is by using certain functions that tell Excel to return an alternative value in place of an error. You can then use the alternative value within other formulas. These include:
- The ISNA function – returns TRUE when the error type is #N/A
- The ISERR function – returns TRUE for all error types except #N/A
- The ISERROR function – returns TRUE for all error types
- The ERROR.TYPE function – returns a number from 1-8 that corresponds to an error-type, as below
1. #NULL!2. #DIV/0!3. #VALUE!4. #REF!5. #NAME?6. #NUM!7. #N/A8. #GETTING_DATA
Want to assign your own value to an error? Excel makes this easy too
IFNA allows you to specify a value to be returned if a formula detects an #N/A error.
Use:
=IFNA(value, value_if_na)
value is the data range you are checking for an #N/A error, and "value_if_na" is the value you wish to return instead of the error.
For example, to change the value returned on a #N/A error to 5, use:
=IFNA(value, 5)
IFERROR lets you define a value to be returned if a formula detects any error type.
Use:
=IFERROR(value, value_if_error)
Where value is the data range you are checking for an error, and "value_if_error" is the value you wish to return instead of the error.
If you would like to change the error to a number, such as 0, to make sure your worksheet continues working in the case of any error arising use:
=IFERROR(value, 0)
You need to sign in or register before you can add a contribution.