Sabrina Ayon
Microsoft Excel offers unique 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 average in excel.
Download Practice Workbook
You can download the workbook and practice with them.
5 Effective Ways to Ignore #N/A Error When Getting Average in Excel
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 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:
AVERAGEIF (range, criteria, [average_range])
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, if we want to calculate the average we all use the average function.
- So, we select the cell (C12) where we want to see the result.
=AVERAGE(C5:C10)- Further, 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 into that selected cell.
=AVERAGEIF(C5:C10, ">=0")- Then, hit the Enter button.
- Finally, you will get the accurate average value without getting an error.
Read More: How to Find Average of Specific Cells in Excel (3 Handy Ways)
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 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.
AGGREGATE(function_num, options, ref1, [ref2], …)
Let’s look at the procedures to use this function.
STEPS:
- In the first place, to use the excel AVERAGE function we are using the same dataset as the previous method.
- Secondly, select the cell (C12) and put the formula there using the function.
=AVERAGE(C5:C10)- Thirdly, press Enter.
- Consequently, we will encounter an error of #N/A.
- We are utilizing the AGGREGATE method to prevent this. Therefore, choose cell (C12) and enter the formula there.
=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 Calculate Average True Range in Excel (with Easy Steps)
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:
IFERROR( formula, alternate_value )
Let’s go through the steps down.
STEPS:
- We must first generate a dataset. 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 devoid of errors.
Read More: How to Fix Divide by Zero Error for Average Calculation in Excel
Similar Readings
- How to Calculate Average Only for Cells with Values in Excel
- Calculate Average of Multiple Ranges in Excel (3 Methods)
- Calculate Average Percentage of Marks in Excel (Top 4 Methods)
- How to Exclude a Cell in Excel AVERAGE Formula (4 Methods)
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 the formula’s output. As we want to get the average so we merge the AVERAGE & IFNA functions to avoid the error. Follow the procedures below.
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 utilizing the AVERAGE & IFNA functions method 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.
Read More: [Fixed!] AVERAGE Formula Not Working in Excel (6 Solutions)
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 employs to construct logical comparisons between a value. The logical function ISERROR is utilized to determine whether or not the cells are referred to contain errors. Let’s see the instructions down.
STEPS:
- To begin with, we’ll utilize 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 technique. 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 the 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.
Conclusion
The above ways will assist you to Ignore NA (#N/A) Error When Getting Average in Excel. Hope this will help you! Please let us know in the comment section if you have any questions, suggestions, or feedback. Or you can glance at our other articles in the ExcelDemy.com blog!
