Skip to main content

NA

The NA function returns the #N/A error (value not available).

This function can be useful for indicating known missing data in calculations to prevent incorrect calculations in dependent formulas.

Syntax​

NA()

The NA function takes no arguments and always returns the #N/A error value.

Examples of use​

If the value in cell A3 is less than zero, this function will replace it with #N/A:

=IF(A3<0, NA(), A3)

This can be useful when constructing charts: error values are ignored and points with negative values will not appear on the chart.

Suppose you are creating a financial model and waiting for data from one department. Instead of using a conditional number (for example, 0, which distorts the average), you can use NA():

=IF(A1="", NA(), A1*1.2)

If cell A1 is empty, the formula will return #N/A, indicating that there is no data. When a number appears in A1, the formula will calculate it with a coefficient of 1.2.

Notes​

  • Using the NA function can help avoid errors in other calculations, as the editor may ignore the #N/A values when applying certain functions, such as SUM, AVERAGE, and so forth.
  • If you want to replace the #N/A error with another value, you can use the IFERROR function.