Exceljet

Quick, clean, and to the point

Excel NA Function

Excel NA function
Summary 

The Excel NA function returns the #N/A error. #N/A means "not available" or "no value available". You can use the NA function to display the #N/A error when information is missing.

Purpose 
Create an #N/A error
Return value 
The #N/A error
Syntax 
=NA ()
Arguments 
Version 
Usage notes 

The NA function returns the #N/A error. #N/A means "not available" or "no value available". You can use the NA function to display the #N/A error when information is missing. Note that if you use the NA function this way, other formulas that depend on cells that contain the #N/A error will also display #N/A, unless you specifically trap and manage the error. The NA function takes no arguments.

Examples

The NA function returns the #N/A error:

=NA() // returns #N/A

You can use the NA function in other formulas. For example, in the formula below, the IF function is configured to test if cell A1 is empty. If so, IF returns NA(), which returns the #N/A error. If A1 is not empty, IF returns A1*B1: 

=IF(A1="",NA(),A1*B1) // #N/A if A1 is empty

You can use the NA function to indicate missing information. In the worksheet shown above, cells C9 and C13 contain the NA function:

=NA()

This indicates that cost is not available. In cell D5, the formula copied down is:

=B5*C5 // qty * cost

In cells D9 and D13, the formula returns #N/A because C9 and C13 contain errors. In cell D15 the SUMIF function is used to sum values in column D while ignoring the #N/A error:

=SUMIF(D5:D13,"<>#N/A") // ignore #N/A

If the SUM function was used instead, it would return #N/A:

=SUM(D5:D13) // would return #N/A

Notes

  • When other formulas refer to cells that contain #N/A, they also return #N/A.
  • NA takes no arguments, but you must provide empty parentheses.
  • You can also enter the value #N/A directly into a cell as text.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.