How to use NA Function in Excel

The Excel NA function returns the #N/A error. #N/A means “not available” or “no value available”. You can nest the NA function inside a formula to display the #N/A error when information is missing.

Syntax:= NA ()

The NA function syntax has the following arguments:

  • The NA function syntax has no arguments.

Example: Let’s look at some Excel NA function examples and explore how to use the NA function as a worksheet function in Microsoft Excel:

NA Function - How to use NA Function in Excel

We need to find out the Total price for all the fruits and do not want 0 in return.

We will use the formula:

Syntax:  =IF(B2=””,NA(),B2*C2)

B2 = “” : condition to check for empty cells that we will return 0
NA() : value if condition is True.
B2*C2 : value if condition is False

As you can see the formula returns value error where condition stands True.

Result:

NA Function in Excel - How to use NA Function in Excel

Based on the Excel spreadsheet above, the following NA examples would return:

Syntax: =IF(B3=””,NA(),B3*C3)
Result: 303.63

Syntax: =IF(B4=””,NA(),B4*C4)
Result: 14.96

Syntax: =IF(B5=””,NA(),B5*C5)
Result: 82.84

Syntax: =IF(B6=””,NA(),B6*C6)
Result: 82.84

Syntax: =IF(B7=””,NA(),B7*C7)
Result: 95.58

Syntax: =IF(B8=””,NA(),B8*C8)
Result: #N/A

Syntax: =IF(B9=””,NA(),B9*C9)
Result: #N/A

Note:

  • When other formulas refer to cells that contain #N/A, they also return #N/A.
  • NA takes no arguments, but you must provide empty parentheses.
  • You can also enter the value #N/A directly into a cell as text.
READ:  How to use ISERROR Function in Excel

Add a Comment

Your email address will not be published. Required fields are marked *