NA Function

The NA function in Excel will return the #N/A Error suggesting to the user that the value in question are ‘not available’ in a given dataset.

There could be instances when you have missing information in a given dataset or the numbers do not fit the preferred criteria in the data.

Files containing blank cells or Incomplete information ‘sometimes’ tend to confuse if you circulate such files internally in the organization or to your clients.

To avoid such simple problems and focus on the greater ones, you can use the NA function in Excel, which will return the #N/A! Error in the given cell.

The function can be combined with other logical functions, such as IF statements which will help you evaluate a bunch of numbers or text strings and ultimately return the #N/A Error if the criteria do not match.

This article will see the NA function, its syntax, and a couple of examples. 

Spoiler alert - Since the function is relatively easy to understand and use, you might finish reading this article before your laptop operating window boots up.

NA function - What is it?

The NA is categorized as an Information function that will return the #N/A Error, meaning ‘value is not available.’

Suppose that you had a couple of empty cells in a given dataset. To make it easy to interpret, we will filter the column to get all the empty cells and then use the NA function to replace them with #N/A Errors.

If you do not wish to work on the same cells, you can use the IF statements to return the #N/A Error if the cell is blank and the value if the cells are non-blank.

The syntax for the function is

=NA()

It does not take in any additional arguments similar to the PI function and returns the result as a #N/A Error.

To use the function, you can begin with an equal sign in the selected cell and type in the function name followed by the parentheses.

Suppose that you have the data in Excel, as illustrated below:

As you can see, we need some of the values in the given dataset. Here, you can add a filter to the ‘Buying Price’ column to get all the blank cells.

Now, all you need to do is input the formula =NA() in those two cells, which after removing the filter, will give the result:

This makes sense when you have a few empty cells. What if thousands of rows of data needed to be evaluated for empty cells or any other criteria?

More on that in the next example.

Example - NA with IF function

Suppose you have the English test scores for the students as illustrated below:

Since it is not feasible to always filter the data, we can add columns and use the IF statements to evaluate data based on certain criteria.

That criterion, in this case, is the presence or absence of empty cells.

We will use the formula =IF(D3="",NA(),""), which gives the result:

We ask the IF statements to evaluate if the cells are empty and if the result returns as TRUE, the NA function works its magic to return the #N/A Error.

It isn’t always necessary that you might want to return the #N/A Error. Instead, you can replace the existing error value with a customized text string.

Once you have added the #N/A errors into the empty cells, you can use the combination of ISNA and IF statements to return a customized text string.

Suppose our data that looks as below:

If we were to use the =IF(ISNA(D3),"Data is missing","") in cell E3 and drag it down to cell E12, we would get the result:

Firstly, we evaluate whether the function is an #N/A Error using the ISNA. Then, the IF statements return two alternative results based on whether the condition is fulfilled to TRUE or FALSE.

SUMIF and NA

Another combination of functions that could be useful to you is the SUMIF and the NA. The general idea is that wherever there is the #N/A Error, we will ignore it and return the sum for the rest of the numbers.

Suppose the sales made for the day by XYZ startup are as below:

As we see, there are some #N/A Errors in our dataset either due to missing information or the item being sold on credit returned using the =NA() formula.

Once we have a clear representation of the data, we can use the formula =SUMIF(D3:D12,"<>#N/A") in cell G5, which gives the result of $5,970.

The formula will ignore all the cells that contain the #N/A Error using our defined criteria and sums up all the cash transactions within our dataset.

Let’s say you were to use the SUM function in this case instead. What would be the result?

If we use the formula =SUM(D3:D12) in cell G5, we will get the result as #N/A Error, which signifies the importance of criteria-based sum when there are errors in our given dataset.

Researched and authored by Akash Bagul | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• TRUE Function
• UPPER Function
• VAR.P Function
• WORKDAY Function
• XIRR Function