Background
When I tried to calculate the average of a range in Excel, I couldn’t get a result because one of the cells contained a #DIV/0! error. I studied how to calculate the average while ignoring this #DIV/0! error, and I will share the method here.
The Function
Here is the function I used. Assuming there are numerical values from A2 to F2, and F2 contains the #DIV/0! error:
=AVERAGE(IF(ISNUMBER(A2:F2), A2:F2))
This formula checks each cell to see if it is a number using ISNUMBER. The IF function then filters the data so AVERAGE only calculates based on the valid numbers, ignoring the error.
Note: If you are using an older version of Excel (Excel 2016 or earlier), you may need to press Ctrl + Shift + Enter to confirm this as an array formula.
Alternative: Using the AGGREGATE Function
There is also a function called AGGREGATE that is specifically designed to ignore errors. It is often easier to use because it doesn’t require array entry.
=AGGREGATE(1, 6, A2:F2)
- 1: Specifies the AVERAGE function.
- 6: Specifies “Ignore error values”.
I hope this helps!
