Help needed in excel formula

Occasional Visitor
I used AVERAGEIF in a column, when condition is "<>0". Problem is, it gives #DIV/0 division error, when all cells have "0".
I want it to return value "0" instead. When all cells have "0",
2 Replies


You can use


or as an alternative =AVERAGEIF(A1:G100,"<>"&0,A1:G100)


Syntax: IFERROR(value, value_if_error)

You can use the IFERROR function to trap and handle errors in a formula. IFERROR returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.


Syntax: AVERAGEIF(range, criteria, [average_range])

If range is a blank or text value, AVERAGEIF returns the #DIV0! error value.

If no cells in the range meet the criteria, AVERAGEIF returns the #DIV/0! error value.


I would be happy to know if I could help.




Was the answer useful? Mark them as helpful!

This will help all forum participants.