G Guest Sep 27, 2007 #1 When I use the average function, I want to ignore values like $0.00, #DIV/0!. How do I do this?
P Peo Sjoblom Sep 27, 2007 #2 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
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
J JE McGimpsey Sep 27, 2007 #3 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.
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.
G Guest Sep 27, 2007 #4 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.
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.
