A to Z of Excel Functions: The NA Function

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 [0], 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.