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

**Table of Contents**hide

**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!