Excel - NA Function

Previous
Quiz
Next

NA Function

The Excel NA function generates the error value #N/A when missing values are encountered in the cells. It is categorized under the Information functions and permits users to highlight empty cell values. This function is highly utilized in complex formulas to handle the error more concisely. For example, if combined with the COUNTIF function, you may count the number of cells that contain the #N/A error and use the ISNA function to inspect the #N/A values.

Compatibility

This advanced Excel function is compatible with the following versions of MS Excel −

  • Excel for Microsoft 365
  • Excel for Microsoft 365 for Mac
  • Excel for the web
  • Excel 2024
  • Excel 2024 for Mac
  • Excel 2021
  • Excel 2021 for Mac
  • Excel 2019
  • Excel 2013
  • Excel 2010
  • Excel 2007

Syntax

The syntax of the NA function is as follows −

=NA( )

Points to Remember

  • The NA function does not contain the argument. Only the function definition =NA() can be specified in the cell to consider it a function. Otherwise, Microsoft Excel does not interpret it as a function when not specifying parentheses and = sign.
  • Inspite of using the NA() function, you can write the value #N/A straightforward in the specific cell,

How to use the NA() Function in Excel?

1. Open a desired Excel worksheet.

2. Place the cursor on the specific cell and double-click on it.

3. Type the = sign and then NA().

4. Then, hit the Enter.

Examples of NA() Function

Practice the following examples to learn the use of the NA function in Excel.

Example 1: Using the IF Statement to Test Whether the Cell is Empty

Solution

Step 1 − Assume the sample dataset consists of the two columns named Value and Result. Enter the formula =NA() in the C2 cell.

Using IF Statement to Test Whether Cell is Empty1

After that, hit the Enter tab. Therefore, the resulting value is #N/A.

Using IF Statement to Test Whether Cell is Empty2

Step 2 − After that, move to the next cell, C3, and write the formula =IF(ISBLANK(B3),NA(),B3+1). In this expression, we will check whether the B3 cell is blank. If the cell B3 is blank, then the NA() function will execute; otherwise, there will be an increment 1 in the B3 cell.

Using IF Statement to Test Whether Cell is Empty3

And then, hit the Enter tab.

Using IF Statement to Test Whether Cell is Empty4

Step 3 − Drag the + sign at the bottom right corner of cell C3 and place it in the C7 cell to calculate the given formula in the remaining cell.

Using IF Statement to Test Whether Cell is Empty5

Example 2: Summation of the Mixed Variety of Values, Including Formula

You enter the numeric and error #N/A values in the specified cell range. Can we get an accurate result if the SUM function is employed in this cell range?

First, enter the formula =SUM(C2:C5) in the C6 cell.

Summation of Mixed Variety of Values1

After that, hit the Enter tab.

Summation of Mixed Variety of Values2

To avoid errors, only numeric value can be added through the SUMIF() function. You can enter the formula =SUMIF(C2:C6,"<>#N/A") in the C7 cell and hit the Enter tab.

Summation of Mixed Variety of Values3

Therefore, the #N/A errors have been ignored, and the sum of the other values to be taken place through the SUMIF function.

Download Practice Sheet

You can download and use the sample data sheet to practice the NA function.

Print Page
Previous
Next
Advertisements