Exceljet

Quick, clean, and to the point

Excel NA Function

Excel NA function
Summary 

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.

Purpose 
Create an #N/A error
Return value 
The #N/A error
Syntax 
=NA ()
Arguments 
Usage notes 

Use NA to generate the #N/A. #N/A means "not available" or "no value available". For example, you can use the NA function to flag cells that are empty or missing information needed for a calculation.

In the example shown, the formula in D8 is:

=IF(B8="",NA(),B8*C8)

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

Notes:

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

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables