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
Select Cell and Type the below formula in the fx function area
=AVERAGE(IFNA(A2:A10, ""))
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, ""))
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 )
