The Excel NA function lets you display the #N/A error value, which means “no value available” or “not available.” It is mainly combined with the IF and other functions in Excel to create complex formulas. It helps you highlight empty cells.
For example, suppose that you are preparing a weekly sales report. You don’t have the sales data for Sunday. Refer to the following image.
Now, you wish to mark the sales on Sunday as #N/A so that the charts can recognize it properly.
We can do this using the NA function. Select the cell B8 (where the sales generated on Sunday is mentioned) and type the following formula:
=N()The results are displayed in the following GIF.
With the NA function, you make sure the data is compatible with other functions and tools like charts, graphs, etc.
Syntax
The NA is a simple function with the following syntax.
=N()The NA function does not require any arguments.
Important Notes:
- Make sure to provide or complete the parentheses and then hit the Enter key.
- When the function is used within other formulas that refer to cells containing the #N/A error, the function also returns #N/A by default.
- Excel also allows you to enter the #N/A (as text) directly into a cell.
How to use the NA Function in Excel
Download the example spreadsheet used to demonstrate the NA function in the upcoming section. Practice is the key to success!
Example 1: NA Function Basics (Combining the NA with the IF function)
Suppose that you are an E-commerce Manager. Your task is to maintain an inventory tracker. The stock is synced in real-time within your online Excel spreadsheet.
You wish to mark the out-of-stock products with #N/A so that there won’t be any errors or miscalculations in further analysis.
For this task, we can combine the NA with the IF function in Excel. The IF function is used to perform a logical test. It returns user-specified output based on whether the logical test is TRUE or FALSE.
Here are the steps:
- Select the cell D2
- Type =IF
- Choose the first option from the popup
- Type B2=”No”
- Specify , to move to the next argument of the IF function
- Type NA
(As the NA is a built-in function in Excel, you will see the following popup) - Double-click the NA command from the list
- Complete the parentheses for the NA function using )
- Type ,
- Specify the cell reference C2
- Complete the parentheses for the IF function using )
- Hit the Enter key
Our formula is as follows:
=IF(B2="No", NA(), C2)Where,
- B2=”No” is the condition to check if the product is out of stock
- NA() is used to display the #N/A error if the product is out of stock
- C2 is the desired output if the product is in stock
The results are displayed in the following image.
Example 2: Calculating the total sum when the given range includes #N/A errors
Suppose that you prepared a table that includes the sales generated by each sales rep in the given month. The data is extracted using the VLOOKUP function. That’s why there are a few #N/A errors in column B. Refer to the following image.
While finding the total sales generated by all of them using the SUM function, you are getting a #N/A error.
The formula we used is as follows:
=SUM(B2:B11)Where B2:B11 is the cell range that includes the sales generated by all sales reps.
The reason this formula is returning the error is that there are #N/A errors in the main table. To solve this problem, we can use the NA with the SUMIF function in Excel.
Our formula would be as follows:
=SUMIF(B2:B11,"<>#N/A")Where,
- B2:B11 is the range that includes the sales generated by individual sales rep
- <>#N/A is the condition that uses not equal to (<>) comparison operator to skip the #N/A errors during the calculation
The results are displayed in the following GIF.
As shown above, the total sales is $1,037,044. By using the comparison operator, we are telling the formula to calculate the total sales without considering the #N/A errors.
Example 3: Finding the number of #N/A errors across the given column
We will consider the previous example.
Here, we have the sales generated by each sales rep. As the data is missing for a few team members, we have #N/A errors in column B.
Our task is to count the number of #N/A errors in column B. The formula would be as follows:
=COUNTIF(B2:B11, "#N/A")Where,
- B2:B11 is the cell range that includes the sales generated by individual sales reps
- #N/A is the criterion for the COUNTIF function
The results are displayed in the following GIF.
Takeaway
NA is one of the rarely used functions in Excel. It helps us mark the empty cells. The function is used to enhance data integrity and analysis.
You can use the IFNA function to check whether the given cell contains a #N/A error and replace it with the custom value.
I hope this article taught you all the bells and whistles of the NA function. Please comment below if you are stuck or encounter any particular error while using it. I will answer your questions as soon as possible.
Additional Resources:
- Learn All Excel Information Functions (With Examples)
- IFNA Function in Excel
- IF Function in Excel
- Types of Errors in Excel
- Guide to Conditional Formatting in Excel
Get an Office 365 Subscription to access all the powerful Functions and Tools in Excel.