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.