Average

[Guest]

When I use the average function, I want to ignore values like $0.00, #DIV/0!.
How do I do this?

 

[Peo Sjoblom]

If you don't want to include zero it doesn't make any sense to include
negative values and then this will work

=SUMIF(A2:A10,">0",A2:A10)/COUNTIF(A2:A20,">0")


although it is advisable to correct the div error in it's source like

=IF(D2=0,"",C2/D2)


--


Regards,


Peo Sjoblom

 

[JE McGimpsey]

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=AVERAGE(IF(ISNUMBER(IF(rng<>0,rng)),rng))

where rng is your range of interest.

 

[Guest]

This answer was extremely helpful. I didn't have to use the average
function, because I cleared up this issues the division issues. Thank you
for being so attentive and detailed.