Help needed in excel formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2869095%22%20slang%3D%22en-US%22%3EHelp%20needed%20in%20excel%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2869095%22%20slang%3D%22en-US%22%3EI%20used%20AVERAGEIF%20in%20a%20column%2C%20when%20condition%20is%20%22%26lt%3B%26gt%3B0%22.%20Problem%20is%2C%20it%20gives%20%23DIV%2F0%20division%20error%2C%20when%20all%20cells%20have%20%220%22.%3CBR%20%2F%3EI%20want%20it%20to%20return%20value%20%220%22%20instead.%20When%20all%20cells%20have%20%220%22%2C%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2869095%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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
use IFERROR(AVERAGEIF(....),0)

@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.