When you carry out complex functions and calculations in a spreadsheet, there is always a possibility of error. In Microsoft Excel, you can use the ISNA function to determine whether the result of another function or cell has resulted in the "#N/A" error value. This error means that the value referred to is not available and can occur in a spreadsheet where a calculation has not resulted in a valid value, normally due to missing data. By using the ISNA function, you can carry out tests determining whether or not such invalid results have occurred, potentially building a level of error handling into your calculations.
- Skill level:
- Moderately Easy
Other People Are Reading
Open Microsoft Excel. If you are working with an existing spreadsheet, open it. Browse to the location you want to use the ISNA function in. If you want your cell to display a true or false value indicating whether another cell is returning the "#N/A" error value, you can start by typing the following into it: =ISNA(
The ISNA function outline appears as follows: ISNA()
Between the brackets is whatever you want to apply the ISNA test to. The result will be a value of either true or false.
Locate the cell you want to check for the error value. When you have the ISNA function followed by the opening bracket typed into your result cell, you can select the cell you want to test for the "#N/A" error value by clicking it or typing its identifier. Once you have the cell identifier in the function, add the closing bracket as in the following example: =ISNA(A1)
Press enter to see the result of your ISNA test. If the cell you included in the brackets of the ISNA function returns the "#N/A" error value, you should see "TRUE" displayed, otherwise you should see "FALSE" displayed.
Build the ISNA function into other functions. In most cases, you are likely to need the ISNA function in conjunction with other processing. For example, you can include more than just a cell reference between the ISNA brackets. You can include other expressions and values in the ISNA function if you need to. You can also use the ISNA function as part of a larger expression. For example, to output a value in another cell unless it returns "#N/A" outputting zero if it does, you could use the following conditional expression: =IF(ISNA(A1), 0, A1)
If cell A1 contains anything other than "#N/A" that value will be output. If cell A1 contains the error value, a value of zero will be output instead.
Tips and warnings
- The ISNA function only returns true for the "#N/A" error value for where the value is not available, for other erroneous values it will return false. Other functions are available for checking different types of error.
- If you are having to check your spreadsheet for potential error values, it may be worth reconsidering the cells producing these problematic results.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for