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, if even one cell in the range you’re averaging has an error, the AVERAGE function returns that error instead of a result. In this tutorial, you’ll learn a few ways to average the values in a range while ignoring the errors.
Ignore #N/A Error
In the following example, one cell in the range has an #N/A error, so when you use the AVERAGE function, it returns #N/A as 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 averages only the cells that meet a condition. In this formula, I have referred to the range A1:A10 and used the condition "<>#N/A", which means “not equal to #N/A”. So every cell except the #N/A cells gets averaged.
This works only when #N/A is the only type of error in the range. If the range also has another error, like #DIV/0! or #VALUE!, that cell still meets the condition and the formula returns that error. To skip every type of error, use one of the methods below.
Using AVERAGEIF to Ignore All the Errors
If you want to ignore every type of error, you can still use AVERAGEIF. You just need to change the criteria in the function.
In this formula, I have used greater than 0 (">0") as the criteria. Error values don’t meet this condition, so AVERAGEIF skips them. But it also skips zeros and negative numbers, so the result is only right when every number in the range is above 0.
For example, if the range has 10, 0, -10 and 40 along with two errors, ">0" returns 25, while the real average of those four numbers is 10. If your range has zeros that should count, use ">=0" instead. If it has negative numbers, use AGGREGATE or AVERAGE with IFERROR (both below), which keep every number. And if you want to leave zeros out on purpose, see Average Number but Exclude Zeros (0).
Use AGGREGATE to Ignore Errors while Averaging Values
AGGREGATE has been in Excel since Excel 2010. It can run AVERAGE and several other calculations, and it gives you the option to ignore errors in the range.
In the first argument, enter the number of the calculation you want. When you start typing, Excel shows a list of functions, so to average values, select AVERAGE from the list (function number 1).
After that, in the second argument, select the option to ignore error values, or just enter 6.
If your data is filtered and you also want to skip the rows you’ve hidden, use 7 instead. It ignores both hidden rows and error values. Now, in the third argument, refer to the range where you have values to average.
In the end, press Enter. AGGREGATE averages all the numbers in the range, including zeros and negative numbers, and skips the errors.
Use AVERAGE with IFERROR to Ignore Errors
Another way is to wrap the range in IFERROR inside AVERAGE: =AVERAGE(IFERROR(A1:A10,""))
IFERROR turns every error in the range into empty text (“”), and AVERAGE ignores text. So only the numbers get averaged, including zeros and negative numbers, just like AGGREGATE.
In Excel 365 and Excel 2021, just press Enter. In Excel 2019 and earlier, this is an array formula, so press Ctrl+Shift+Enter instead, or the formula won’t work as expected.
In Excel 365 and Excel 2021, you can also use FILTER to keep only the numbers and then average them: =AVERAGE(FILTER(A1:A10,ISNUMBER(A1:A10))). If the range has no numbers at all, FILTER returns a #CALC! error.
Average with a Condition and Ignore Errors
Sometimes you need the average for one region, product or month, and the values still have errors. AGGREGATE can’t help here, because its average option only works on a plain range. In the formulas below, column A has the regions and column C has the values.
The quickest way is AVERAGEIFS with a second condition that cells with errors can’t meet: =AVERAGEIFS(C2:C10,A2:A10,"East",C2:C10,">=0")
Just like with AVERAGEIF, the “>=0” condition also leaves out negative numbers. To keep every number, use AVERAGE with IF and ISNUMBER instead: =AVERAGE(IF((A2:A10="East")*ISNUMBER(C2:C10),C2:C10))
Here, the first part checks the region and ISNUMBER skips the errors. In Excel 2019 and earlier, press Ctrl+Shift+Enter to enter it. In Excel 365 and Excel 2021, you can write the same thing with FILTER: =AVERAGE(FILTER(C2:C10,(A2:A10="East")*ISNUMBER(C2:C10)))
Fix the Errors in the Source Data
All of these methods hide the errors in the average, but the errors are still in your data. If they come from a formula, it’s often better to handle them there. For example, when a lookup can’t find a value, it returns #N/A. You can wrap it in IFNA (Excel 2013 and later) to return empty text instead: =IFNA(VLOOKUP(E2,A2:B10,2,FALSE),"")
AVERAGE skips empty text, so a plain AVERAGE now works on that column. Don’t return 0 here, because AVERAGE would count it and change the result. And if you want to catch every type of error, not just #N/A, use IFERROR instead of IFNA.
Which Method Should You Use?
- AVERAGEIF with “<>#N/A”: when #N/A is the only type of error in your data.
- AVERAGEIF with “>0” or “>=0”: quick, but it leaves out negative numbers (and zeros with “>0”).
- AGGREGATE: skips every error and keeps all the numbers. Use option 7 to skip hidden rows as well.
- AVERAGE with IFERROR: skips every error and keeps all the numbers, but needs Ctrl+Shift+Enter in Excel 2019 and earlier.
- AVERAGEIFS or AVERAGE with IF: when you also need a condition, like one region or one month.
- Google Sheets: AGGREGATE isn’t available there, so use AVERAGEIF, or AVERAGE with IFERROR wrapped in ARRAYFORMULA.
Frequently Asked Questions
How do I average and ignore #N/A in Excel?
If #N/A is the only error in the range, use =AVERAGEIF(A1:A10,"<>#N/A"). If the range has other errors too, use =AGGREGATE(1,6,A1:A10), which skips every type of error.
Does AVERAGE ignore blank cells?
Yes. AVERAGE skips empty cells and cells with text, including formulas that return empty text (“”). Cells with 0 are counted, though.
Why does AVERAGEIF return #DIV/0!?
AVERAGEIF returns #DIV/0! when no cell in the range meets the criteria, for example when every cell is an error. Check the criteria, or wrap the formula in IFERROR to show a message instead.
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.