AVERAGE returns an error if any cell in the range holds one, so use AGGREGATE instead: =AGGREGATE(1,6,A1:A10). The 1 selects AVERAGE and the 6 tells it to ignore error values — it handles #N/A, #DIV/0!, #VALUE!, and the rest in one formula, in every version since Excel 2010.
If you only need to skip #N/A, =AVERAGEIF(A1:A10,"<>#N/A") works too, but it won’t help with any other error type, so AGGREGATE is the safer default. Two things worth knowing.
Ignoring the errors doesn’t fix them — if the #N/A comes from a VLOOKUP that isn’t finding a match, the average is being taken over incomplete data, so it’s usually better to wrap the source formula in =IFNA(VLOOKUP(...),"") and average a clean column. And in Excel 365, =AVERAGE(FILTER(A1:A10,NOT(ISERROR(A1:A10)))) does the same job with a more readable structure.
In Excel, when you try to average values from a range, but you have an error in one or more cells, your formula will return an error in the result. In this tutorial, we will learn to average values from a range but ignore the error values at the same time.
Ignore #N/A Error
In the following example, we have an #N/A error in the range of cells, and when you use the AVERAGE function it returns #N/A errors in the result as well.
To deal with this situation, you can use the AVERAGEIF function in the following way.
=AVERAGEIF(A1:A10,"<>#N/A")AVERAGEIF allows you to create a condition to average values from a range. And in this formula, we have referred to the range A1:A10, and after that, we have used a condition that averages values that are not equal to #N/A.
Using AVERAGEIF to Ignore All the Errors
If you want to ignore all the errors, you can still use AVERAGEIF. You just need to change the criteria in the function.
In this formula, we have used greater than 0 in the criteria. When you do this, Excel only refers to the numbers from the range and averages only those cells with numbers.
Use AGGREGATE to Ignore Errors while Averaging Values
AGGREGATE is a new function that can help you to average values, but with this function, you have the option to ignore errors from the range.
In the first argument of the function, you need to use the function number. This means if you want to average values, select the function from the list.
After that, in the second argument, select the option to ignore the errors, or just enter 6.
Now, in the third argument, refer to the range where you have values to average.
In the end, hit enter to get the result in the cell.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.