Syntax. NA()

Definition. This function returns the #N/A error if a value doesn’t exist.

Arguments. This function does not use arguments.

Background. Although this function doesn’t use arguments, you have to enter the parentheses; otherwise, Excel recognizes the function as text.

Similar to the TRUE() and FALSE() functions, in which you can enter the corresponding logical values, you can enter the #N/A error value for the NA() function to get the same result. The NA() function is provided for compatibility with other spreadsheet applications.

You can use this function or the result to explicitly mark empty cells. This ensures that empty cells are not used for other calculations. Formulas referring to these cells return the same error value. You should therefore use this function with care.

Example. In the example for the ISBLANK() function, you checked whether empty cells existed so that you could exclude those cells from a calculation. If you used the NA() function instead, you wouldn’t get the desired result (see Figure 11-7).

Figure 11-7. An alternative to the example shown in Figure 11-4.

Column C contains the references to column B, and the average should be calculated in cell D2. If C3 contained a 0, it wouldn’t match column B. Therefore, you marked the empty cell in column B as empty (=NA() or #N/A). Unfortunately, the #N/A error value is used in all calculations.

=IF(ISNA(B7),"",B7)

This returns the correct result, which you would also get with the ISBLANK() function.