How to use the Excel NA function

Summary

The Excel NA function returns the #N/A error. #N/A means "not available" or "no value available". You can nest the NA function inside a formula 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

Usage notes

Use NA to generate the #N/A. #N/A means "not available" or "no value available". For example, you can use the NA function to flag cells that are empty or missing information needed for a calculation.

In the example shown, the formula in D8 is:

=IF(B8="",NA(),B8*C8)

When the quantity is missing, there isn't enough data to run the calculation.,so the formula checks to see if the cell in column B is blank. If so, it returns the #N/A error using NA. If a value exists, the normal calculation is run.

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.

Related functions

Excel ISNA Function

The Excel ISNA function returns TRUE when a cell contains the #N/A error and FALSE for any other value, or any other error type. You can use the ISNA function with the IF function test for an error and display a friendly message when it appears...

Excel ISERR Function

The Excel ISERR function returns TRUE for any error type except the #N/A error. You can use the ISERR function together with the IF function to test for an error and display a custom message, or perform a different calculation if found.

Excel IFERROR Function

The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.