How to Ignore na Values with Formulas in Google Sheets(Quick & Easy Guide)

In this tutorial, you will learn How to Ignore na Values with Formulas in Google Sheets. (#N/A error)

To Ignore na ( #N/A) Values with Formulas in Google Sheets you can use the below formula.

=AVERAGE(IFNA(A2:A10, "")) 

=SUM(IFNA(A2:A10, ""))

=IFNA(VLOOKUP(E2, A2:E12, 2, FALSE), "")

The above formulas will substitute blank values for #N/A values to calculate the required values.

Let’s see how to use the above-mentioned formulas with examples.

How to Ignore NA Values with Formulas in Google Sheets: Step-by-Step Guide

Here are the steps to Ignore #N/A Values with Formulas in Google Sheets

# Average Calucluation while ignoring #N/A Values

In the below example, we have a dataset for which we will perform average calculation while ignoring #N/A

Avoid The #NA Error in the VLOOKUP ...
Avoid The #NA Error in the VLOOKUP Formula

Select Cell and Type the below formula in the fx function area

=AVERAGE(IFNA(A2:A10, "")) 
How to Ignore #N/A Values with Formulas in Google Sheets

The calculated average value for the range of data is 7.5

#  Sum Calculation while ignoring #N/A Values

In the following example, you will see how to perform a Sum calculation for a range of data which includes #N/A values.

We have used the below formula to perform the calculation

=SUM(IFNA(A2:A10, ""))
How to Ignore #N/A Values with Formulas in Google Sheets

The calculated SUM for the selected range of data while ignoring #N/A values is 45.

# Perform VLOOKUP while Ignoring #N/A Values

In the following example, we will use the VLOOKUP function to get the number of Points scored in the New points column that matches the value in the Teams Column.

We will use the below formula :

=IFNA(VLOOKUP(E2, $A$2:$C$12, 2, FALSE), "")

You will notice that for any value which is equal to #N/A the  VLOOKUP function will return a blank value instead of returning the #N/A value

How to Ignore NA Values with Formulas in Google Sheets : (Conclusion)

In the above tutorial, we have walked you through a step-by-step guide using which you can Ignore #N/A Values with Formulas in Google Sheets.

We hope this tutorial on How to Ignore #N/A Values with Formulas in Google Sheets was helpful.

Related articles :

How to Insert Spin Button in Google Sheets ( Easy Guide )

How to Freeze Rows in Google Sheets

How to Calculate Square Root and Cube Root in Google Sheets(Quick & Easy Guide)

How to use SUMSQ Function in Google Sheets(Quick & Easy Guide )