NA Function

What is the NA Function?

The NA Function is categorized under Excel Information functionsFunctionsList of the most important Excel functions for financial analysts. This cheat sheet covers 100s of functions that are critical to know as an Excel analyst. It will return the #N/A error value. N/A means “no value available” or “not available.”

As a financial analystFinancial Analyst Job DescriptionThe financial analyst job description below gives a typical example of all the skills, education, and experience required to be hired for an analyst job at a bank, institution, or corporation. Perform financial forecasting, reporting, and operational metrics tracking, analyze financial data, create financial models, the NA function can be used to mark empty cells and thus avoid the inclusion of empty cells in the calculation. It can also be used to highlight cells where information is missing.

Formula

=NA()

The NA function does not require an argument.

How to use the NA Function in Excel?

To understand the uses of the NA function, let’s consider a few examples:

Example 1

Suppose we are given the following data:

In the data above, we are aware that certain cells are blank. To find out the total amount instead of using the formula Units * Sales price, we will use the formula:

We get the result below:

Now, if the quantity is missing and there isn’t enough data to run the calculation, the formula checks to see if the cell in column B is blank. If it is, it returns the #N/A error using the NA function. If a value exists, the normal calculation would be run.

Example 2

When we are given large datasets, VLOOKUPs can be slow to run. In such a scenario, we can use the NA function and two VLOOKUPs to complete things faster.

Suppose the data above ran into a million rows. In such a scenario, the simple VLOOKUP would be painfully slow. Instead, we can use the formula below:

We get the result below:

In the formula above, we used NA() so we get an #N/A error, but we can also return a message like “Missing” or “Not found,” as shown below:

For the formula to work, we need to ensure that the data is sorted by the LOOKUP value. So, the roll numbers in the data above are sorted accordingly.

A few things to remember about the NA Function

1. We need to include empty parentheses along with the function name. If not included, MS Excel will not recognize it as a function.
2. The NA function was provided for compatibility with other spreadsheet programs.
3. When other formulas refer to cells that contain #N/A, they also return #N/A.

Click here to download the sample Excel file

Additional resources

Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these Excel functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional CFI resources:

• Excel Functions for FinanceExcel for FinanceThis Excel for Finance guide will teach the top 10 formulas and functions you must know to be a great financial analyst in Excel. This guide has examples, screenshots and step by step instructions. In the end, download the free Excel template that includes all the finance functions covered in the tutorial
• Advanced Excel Formulas Course
• Advanced Excel Formulas You Must KnowAdvanced Excel Formulas Must KnowThese advanced Excel formulas are critical to know and will take your financial analysis skills to the next level. Advanced Excel functions
• Excel Shortcuts for PC and MacExcel Shortcuts PC MacExcel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts