Microsoft Excel offers lots of functions, one of which is the AVERAGE function. This computes the average of a value in a range of cells. However, there are instances in which a range of cells are empty or could carry zeroes, which could have an impact on the outcome. When we use this function to compute the average, this returns the #N/A error. The error code #N/A indicates that no value is available. In this article, we will demonstrate some effective ways to ignore the NA (#N/A) error when getting an average in Excel.
How to Ignore #N/A Error When Getting Average in Excel: 5 Effective Ways
The AVERAGE function is classified under Excel Statistical functions. The function employs the mean or average of a set of inputs. But sometimes, we will receive an error message if we use the AVERAGE function to average a group of cells that includes some wrong values. Let’s follow the ways to ignore the NA (#N/A) error when getting an average in Excel.
1. Ignore #N/A Error with AVERAGEIF Function in Excel
To overlook the #N/A error while calculating the average of a group of cells with some null value, we are going to use the AVERAGEIF function. This function computes the average of a value in a range of cells as well as the average of cells based on specific conditions. The syntax of the function is:
Let’s see the steps down to use this function to ignore the error.
STEPS:
- Firstly, we need to create a dataset. Suppose, we have some students’ names and their marks for a specific subject. Now, we want to compute the average of those students’ marks. Some of the students were absent during the exam period that’s why they have the #N/A value in their mark column.
- As we know, to calculate the average we all use the average function.
- So, we select cell C12 and insert the formula to see the result.
=AVERAGE(C5:C10)- Press Enter.
- Unfortunately, we will get an error which is the #N/A error.
- To avoid this, we are using the AVERAGEIF function. So, choose cell C12 and insert the formula.
=AVERAGEIF(C5:C10, ">=0")- Then, hit the Enter button.
- You will get the accurate average value without any error.
Read More: [Fixed!] AVERAGE Formula Not Working in Excel
2. Use Excel AGGREGATE Function for Ignoring #N/A Error Values
Another way to ignore the NA (#N/A) error while getting the average is by using the AGGREGATE function. When using the AGGREGATE function, we are able to neglect error values while applying various aggregate functions to a range of data.
Let’s look at the procedures to use this function.
STEPS:
- To use the Excel AVERAGE function, we are using the same dataset as the previous method.
- Select cell C12 and put the formula using the function.
=AVERAGE(C5:C10)- Then, press Enter.
We encounter an error of #N/A.
- We are using the AGGREGATE function to prevent this. Therefore, choose cell C12 and enter the formula below.
=AGGREGATE(1,6,C5:C10)- Next press the Enter key.
- Lastly, you will obtain the average number accurately and without error.
Read More: How to Fix Divide by Zero Error for Average Calculation in Excel
3. Combine IFERROR and AVERAGEIF Functions to Avoid Error When Getting Average
We combine the IFERROR & AVERAGEIF functions to ignore the NA (#N/A) error in Excel. Microsoft Excel’s IFERROR function produces a different value if a formula fails. The syntax of the IFERROR function is:
Let’s go through the steps down.
STEPS:
- Let’s say we know the names of certain students as well as their grades for a particular subject. Now, we want to figure out what those students’ average grades are. Some students missed the exam session, which is why their mark column has a #N/A value.
- We all use the AVERAGE function to determine the average since we are aware of this.
- We thus choose cell C12Â to display the outcome in.
=AVERAGE(D5:D10)- Press Enter.
- Unfortunately, we will encounter an error of #N/A.
- Using the combination of the IFERROR & AVERAGE functions, we are able to prevent this. Choose cell C12Â and enter the formula in that cell.
=IFERROR(AVERAGEIF($D5:$D10,"<>#N/A"),"")- After that, press the Enter key.
- Finally, you will have an accurate average number.
4. Merge AVERAGE and IFNA Functions to Ignore #N/A Error in Excel
In cases where a formula produces the #N/A error value, the IFNA function shows the value you provide. In all other cases, it gives an output. As we want to get the average we merge the AVERAGE & IFNA functions to avoid the error.
STEPS:
- Likewise the previous method, we are going to use the same dataset.
- Now, select the cell C12Â and write down the formula there.
=AVERAGE(C5:C10)- Hit Enter.
- But we get an error.
- We are applying the AVERAGE & IFNA functions to prevent this. Therefore, choose cell C12Â and enter the formula there.
=AVERAGE(IFNA(D5:D10, ""))- Then, press the Enter key.
- In the end, you will obtain the precise average.
5. Combination of AVERAGE, IF & ISERROR Functions to Skip #N/A Error for Getting Average Value
If we use the combination of AVERAGE, IF & ISERROR functions, we can ignore the #N/A error. The IF function is employed to construct logical comparisons between a value. The logical function ISERROR is used to determine whether or not the cells are referred to contain errors.
STEPS:
- To begin with, we’ll use the same dataset that we did with the prior approach.
- Next, select the cell C12Â and enter the formula there at this time.
=AVERAGE(C5:C10)- Subsequently, hit the Enter key.
- But an error occurs.
- To avoid this, we are using the combination of AVERAGE, IF & ISERROR functions. Choose cell C12Â and type the formula there as a result.
=AVERAGE(IF(ISERROR(D5:D10),"",D5:D10))- Press the Enter key after that.
- You will ultimately arrive at an accurate average.
Things to Keep in Mind
- While inputting any function, the function name must be enclosed in empty brackets. If not, Excel will fail to identify it as a function.
- Alternatively, we can enter the value #N/A into a cell. For compatibility with other spreadsheet programs.
Download Practice Workbook
You can download the workbook and practice with them.
Conclusion
The above ways will assist you to ignore the NA (#N/A) error when getting an average in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback.
Related Articles
- How to Average a Column in Excel
- How to Calculate Average of Multiple Columns in Excel
- How to Calculate Average of Multiple Ranges in Excel
- How to Calculate Average Only for Cells with Values in Excel
- How to Find Average of Specific Cells in Excel
- How to Find Average with Blank Cells in Excel
- How to Average Only Visible Cells in Excel
- How to Exclude a Cell in Excel AVERAGE Formula
- How to Average Every Nth Row in Excel
