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.