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:

**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, 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.

**AGGREGATE(function_num, options, ref1, [ref2], â€¦)**

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:

**IFERROR( formula, alternate_value )**

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

Hi there, why would the =IFERROR(AVERAGEIF($D5:$D10,”#N/A”),””) need a dollar sign before the column?

Hello

Liz,You can avoid the absolute reference ($ sign). Here absolute reference is used so that it can avoid error if anyone copy the formula in another cell.

Regards

ExcelDemy