How to use the ISNA function
What is the ISNA function?
The ISNA function returns TRUE if the returned value is a #N/A error.
Table of Contents
1. Introduction
What is a #N/A error?
The #N/A error happens when a value is not available for a formula or found in a given cell range, for example in the VLOOKUP or MATCH functions.
What is a Boolean value?
A Boolean value in Excel is a value that can only be TRUE or FALSE. It represents binary logic and is the result of a logical expression using logical operators or a result of a few Excel functions like ISNA function that I'll discuss below. Mastering Boolean logic and logical expressions is key to manipulating data and controlling workflow in Excel.
- Boolean value TRUE has the numerical equivalent of 1.
- Boolean value FALSE has the numerical equivalent of 0 (zero).
All functions that begins with IS return boolean value TRUE or FALSE. The IF function and SUMPRODUCT function are two functions of many that use boolean logic to calculate an output value.
In Excel, you can use the logical operators AND and OR to combine boolean values (TRUE and FALSE) to create more complex logical expressions. Here's how they work:
- AND Logic: The AND operator returns TRUE if all the conditions it is evaluating are TRUE, and FALSE if any of the conditions are FALSE.
The syntax for the AND function in Excel is: =AND(logical1, [logical2], ...)
Example: =AND(A1=10, B1>5)
This will return TRUE only if the value in cell A1 is 10 AND the value in cell B1 is greater than 5. - OR Logic: The OR operator returns TRUE if any of the conditions it is evaluating are TRUE, and FALSE if all the conditions are FALSE.
The syntax for the OR function in Excel is: =OR(logical1, [logical2], ...)
Example: =OR(A1=10, B1>5)
This will return TRUE if either the value in cell A1 is 10 OR the value in cell B1 is greater than 5.
Here are some examples using boolean values:
- =AND(TRUE, TRUE) - Returns TRUE
- =AND(TRUE, FALSE) - Returns FALSE
- =OR(TRUE, FALSE) - Returns TRUE
- =OR(FALSE, FALSE) - Returns FALSE
You can also use these logical operators in combination with other Excel functions, such as IF statements, to create more complex conditional logic. For example:
- =IF(AND(A1>0, B1>0), "Both Positive", "Not Both Positive")
- =IF(OR(A1>0, B1>0), "At Least One Positive", "Neither Positive")
Understanding AND and OR logic in Excel can be very useful when you need to perform complex data analysis or make decisions based on multiple conditions.
Other IS functions
| Excel Function | Description |
|---|---|
| ISBLANK(value) | Returns TRUE if the value is empty, FALSE otherwise |
| ISERR(value) | Returns TRUE if the value is any error value except #N/A, FALSE otherwise |
| ISERROR(value) | Returns TRUE if the value is any error value, FALSE otherwise |
| ISEVEN(value) | Returns TRUE if the value is an even number, FALSE for odd numbers |
| ISFORMULA(reference) | Returns TRUE if the cell contains a formula, FALSE otherwise |
| ISLOGICAL(value) | Returns TRUE if the value is a logical value (TRUE/FALSE), FALSE otherwise |
| ISNA(value) | Returns TRUE if the value is the #N/A error, FALSE otherwise |
| ISNONTEXT(value) | Returns TRUE if the value is not text, FALSE if it is text |
| ISNUMBER(value) | Returns TRUE if the value is a number, FALSE otherwise |
| ISODD(value) | Returns TRUE if the value is an odd number, FALSE for even numbers |
2. Syntax
ISNA(value)
| value | Required. A cell reference pointing to the value you want to check for #N/A error. |
What is a cell reference?
A cell reference lets you "fetch" and use values in other cells in a formula.
There are two types of cell references:
- A1-style reference
- R1C1 reference
The A1-style reference is the default style in Excel, it names columns by letters from A to Z. After Z it starts over with AA, AB, and so on until XFD. Rows are numbered from 1 to 1048576, older Excel versions use less row numbers.
The R1C1-style uses row number and column number like: R1C1, R2C5 and R10C15. Rows are labeled R1, R2, R3 and so on, columns are labeled C1, C2, C3 etc.
The A1-style reference notation is the most common one, here are some examples:
A1 - single cell reference on the same worksheet
A1:D5 - reference to a cell range on the same worksheet
Budget!Z3 - a single cell reference to worksheet Budget
'Budget 2050'!A3 - a single cell reference to a worksheet containing a space character
There are two types of cell references:
- Relative cell references
- Absolute cell references
The examples above are all relative cell references, they change accordingly if a cell is copied and pasted to another cell which absolute cell references do not.
The $ dollar character lets you an absolute cell reference meaning you can lock a cell reference horizontally, vertically or both. Here is one example:
A$1 has a relative column reference but an absolute row reference, this means that the column letter may change if the cell is copied and pasted to cells in another column than A.
3. Example 1
This image above demonstrates the behavior of Excel's ISNA function, which returns TRUE only when the input is the #N/A error value, and FALSE for all other inputs. Cell range B3:B9 contains input values and cell range C3:C9 contains the output fomr the ISNA function.
The first source value in cell B3 is a text value.
Formula in cell C3:
The output value in cell C3 is boolean value FALSE. Text value "A" is not equal to #N/A error value which results in boolean value FALSE.
Here is a break-down of the formulas in the remaining cells C4:C9:
- The source value in cell B4 is a numeric value. The output value in cell C4 is boolean value FALSE. Numeric value 1 is not equal to #N/A error value which results in boolean value FALSE.
- The source value in cell B5 is a #DIV/0! error value. The output value in cell C5 is boolean value FALSE. #DIV/0! error value is not equal to #N/A error value which results in boolean value FALSE.
- The source value in cell B6 is a #N/A error value. The output value in cell C6 is boolean value TRUE. #N/A error value is equal to #N/A error value which results in boolean value TRUE.
- The source value in cell B7 is a #VALUE! error value. The output value in cell C7 is boolean value FALSE. #VALUE! error value is not equal to #N/A error value which results in boolean value FALSE.
- The source value in cell B8 is a #NAME? error value. The output value in cell C8 is boolean value FALSE. #NAME? error value is not equal to #N/A error value which results in boolean value FALSE.
- The source value in cell B9 is a #REF! error value. The output value in cell C9 is boolean value FALSE. #REF! error value is not equal to #N/A error value which results in boolean value FALSE.
'ISNA' function examples
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Table of Contents Automate net asset value (NAV) calculation on your stock portfolio Calculate your stock portfolio performance with Net […]
Functions in 'Information' category
The ISNA function function is one of 19 functions in the 'Information' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form