Help needed in excel formula

[bajwa2u]

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]

use IFERROR(AVERAGEIF(....),0)

[#3]

@bajwa2u 

You can use

=IFERROR(AVERAGEIF(A1:G100,"<>0"),"")

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.

NikolinoDE

Was the answer useful? Mark them as helpful!

This will help all forum participants.