NA
TheNA
function in Google Sheets is not actually a function that performs a calculation. Instead, it is used to represent a missing or nonexistent value in a cell or formula. When you use the NA
function, it returns the error value #N/A, which tells you that there is no value in that cell or that a formula is missing an argument or reference. This can be helpful for troubleshooting errors in your spreadsheet.
Usage
Use the NA
formula with the syntax shown below:
Examples
Here are a few example use cases that explain how to use theNA
formula in Google Sheets.
Handling missing data
If you have data that is missing in your spreadsheet, you can use the NA
function to represent those missing values. This can be helpful when you are performing calculations or creating charts, as it allows you to more easily identify which data is missing.
Troubleshooting errors
If you encounter an error in your spreadsheet, such as a formula that is missing a reference or argument, you can use the NA
function to help identify where the error is occurring. By using the NA
function in place of missing data, you can quickly see which cells or formulas are causing the error.
Creating blank cells
If you want to create a blank cell in your spreadsheet, you can use the NA
function. This is particularly useful if you want to create a blank space in a chart or graph, or if you want to create a template for data entry.
Common Mistakes
NA
not working? Here are some common mistakes people make when using the NA
Google Sheets Formula:
Using NA as a value
One common mistake is to use NA as a value in a cell. This can cause unexpected results in formulas that use the cell, as NA is a reserved value that indicates a missing or unknown value. To correct this, use a different value to represent missing or unknown data, such as an empty cell or a specific text string.
Using NA in a formula
Another common mistake is to use NA in a formula as an argument or operand. This can cause the formula to return unexpected results, as NA is a special value that propagates through some formula calculations. To correct this, use a different function or formula that handles missing or unknown data appropriately, such as IFERROR, ISBLANK, or IFNA.
Not understanding NA propagation
A common mistake when working with NA is not understanding how it propagates through formula calculations. NA can act as a 'poison pill' that causes some functions to return NA as well, even if other arguments or operands are valid. To avoid this, use functions and formulas that handle missing or unknown data explicitly, or use conditional logic to check for NA values before using them in further calculations.
Related Formulas
The following functions are similar to NA
or are often used with it in a formula:

ISERROR
The
ISERROR
formula is used to check if a value contains an error. This formula returnsTRUE
if the value is an error, andFALSE
if it is not. This function is most commonly used in combination with other formulas that can return errors, to ensure that the resulting value is valid. 
IFERROR
The
IFERROR
formula is used to check whether a specified value results in an error or not. If the value results in an error, then it returns a userspecified value instead of the error. This function is commonly used to prevent errors from breaking a formula or to replace error messages with custom messages. 
ERROR.TYPE
The
ERROR.TYPE
formula returns a number indicating the type of error value in the givenreference
. This formula is commonly used to identify error types and act accordingly, for example, to replace specific error types with a custom message or value.
Learn More
You can learn more about the NA
Google Sheets function on Google Support.