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:
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:
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.
