# How to Ignore #N/A Error When Getting the Average in Excel (5 Methods)

### Method 1 – Using the AVERAGEIF Function in Excel

STEPS:

• We need to create a dataset. We have some students’ names and their marks for a subject. 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.

• Select cell C12 and enter the formula :
`=AVERAGE(C5:C10)`
• Press Enter.
• Unfortunately, we will get a #N/A error.

• To avoid this, we are using the AVERAGEIF function.
• Choose cell C12 and insert the formula:
`=AVERAGEIF(C5:C10, ">=0")`
• Press Enter.

You will get the accurate average value without any errors.

### Method 2 – Using the Excel AGGREGATE Function for Ignoring #N/A Error Values

STEPS:

• Use the same dataset as the previous method.
• Select cell C12 and enter the following formula:
`=AVERAGE(C5:C10)`
• Press Enter.

We get an errorÂ #N/A.

• Use the AGGREGATE function to prevent this.
• Choose cell C12 and enter the formula below:
`=AGGREGATE(1,6,C5:C10)`

### Method 3 – Combining IFERROR and AVERAGEIF Functions to Avoid Error When Getting Average

STEPS:

• We want to determine those students’ average grades. Some students missed the exam session, so their mark column has a #N/A value.

• Use the AVERAGE function to determine the average.
• Choose cell C12 and enter the following formula:
`=AVERAGE(D5:D10)`
• Press Enter.
• Unfortunately, we will encounter an error of #N/A.

• We can prevent this by combining the IFERROR and AVERAGE functions.
• Choose cell C12 and enter the following formula:
`=IFERROR(AVERAGEIF(\$D5:\$D10,"<>#N/A"),"")`
• Press Enter.
• You will have an accurate average number.

### Method 4 – Merging the AVERAGE and IFNA Functions to Ignore #N/A Error in Excel

STEPS:

• Use the same dataset.
• Select the cell C12 and enter the following formula:
`=AVERAGE(C5:C10)`
• Press Enter.
• But we get an error.

• We are applying the AVERAGE & IFNA functions to prevent this.
• Choose cell C12 and enter the following formula:
`=AVERAGE(IFNA(D5:D10, ""))`
• Press Enter.
• You will get the precise average.

### Method 5 – Using a Combination of AVERAGE, IF & ISERROR FunctionsÂ

STEPS:

• Use the same dataset.
• Select the cell C12 and enter the following formula:
`=AVERAGE(C5:C10)`
• Press Enter.
• But an error occurs.

• To avoid this, we are using the combination of AVERAGE, IF & ISERROR functions.
• Choose cell C12 and enter the following formula:
`=AVERAGE(IF(ISERROR(D5:D10),"",D5:D10))`
• Press Enter.
• You will 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.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Sabrina Ayon

Sabrina Ayon, a Computer Science and Engineering graduate from United International University, has been an integral part of the ExcelDemy project for two years. She authored 150+ articles, excelling in instructing through visually engaging Excel tutorials. With a passion for teaching, Sabrina conducted sessions on Excel VBA, sharing her knowledge and insights with others. Currently holding the position of Project Manager for the ExcelDemy Visual Development Project, she oversees various aspects of the project, ensuring its smooth operation... Read Full Bio

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

Advanced Excel Exercises with Solutions PDF