# How to Ignore #N/A Error When Getting Average in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

### 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)`

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

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Team Leader. I'm a graduate in BSc in Computer Science and Engineering from United International University. I love working with computers and solving problems. Iâ€™ve always been interested in research and development. Here I post articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF