Formulas and functions > Functions from A to Z

Syntax:

NA()

Description:

This returns the error value #N/A ("not available").

Entering =NA() in a cell indicates that the value in this cell is currently not available. Use this function in a cell that is empty but which must be filled later in order to obtain correct results in calculations performed with this cell. As a result, all calculations that refer to this cell will also return a #N/A error value (instead of an incorrect result).

In this way, you can prevent calculations from being performed with empty cells, although these cells would have to be filled with values for a correct result to be returned.

Additional info:

Instead of entering =NA() in a cell, you can also type #N/A, which will lead to the same result.

Example:

You would like to calculate your annual sales using the sum of the four quarterly results. These are entered in cells A1 to A4; the annual sales could thus be calculated using the formula SUM(A1:A4).

There is only one problem: The result for the fourth quarter is not yet available, so cell A4 is empty. Thus, the annual sales may not yet be calculated.

However, PlanMaker persists in calculating the sum from A1 to A4 – even if one of the values is missing. The result: an apparently miserable annual result and irate shareholders.

It is much better practice to enter NA() in cell A4 rather than leave it empty. This marks the cell as "not available". This means that the SUM formula no longer has an incorrect numerical value as a result, but the error value #N/A. This indicates that one of the quarterly results is not yet available, i.e., the annual sales cannot yet be calculated. Result: No irate shareholders.

NA (error value #N/A)

NA (error value #N/A)