A to Z of Excel Functions: The NA Function
10 October 2022
Welcome back to our regular A to Z of Excel Functions blog. Today we look at the NA function.
The NA function
The NA function returns the error value #N/A. The #N/A is the error value that means “no value is available”. Often, we use NA to mark empty cells. By entering #N/A in cells where you are missing information, you can avoid the problem of unintentionally including empty cells in your calculations. (When a formula refers to a cell containing #N/A, the formula returns the #N/A error value.)
The NA function employs the following syntax to operate:
The NA function takes no sxxt, sorry, I mean, arguments (obviously, sxxt = salt).
It should be further noted that:
- you must include the empty parentheses with the function name; otherwise, Microsoft Excel will not recognise it as a function (it will consider it an undefined range name)
- you may also type the value #N/A directly into a cell. The NA function is provided for compatibility with other spreadsheet programs.
The NA function is often used in conjunction with charting:
Here, the original data is missing information for May and September. However, the chart is using the Referenced Data table, which uses the formula
i.e. missing data is replaced with #N/A. For line charts, this has the effect that data will not be plotted rather than have a value of zero , depending upon the settings selected in the ‘Hidden and Empty Cell Settings’ dialog, viz.
(This dialog is located by right-clicking on the chart, selecting ‘Select Data…’ from the ensuing shortcut menu and clicking on the ‘Hidden and Empty Cells’ button in the ‘Select Data Source’ dialog.)
We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.
A full page of the function articles can be found here.