Average
 

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

Average
 

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


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

Average
 

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.

In article ,
Mike wrote:

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

Average
 

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.

"Peo Sjoblom" wrote:

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


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