Excel: Dealing with #DIV/0! 1

[krets]

I have a formula that conditionally averages the non-zero values of a column:

{=AVERAGE(IF(E192:E199<>0,E192:E199))}

Sometimes though, all the values are zero and the result will be the good old #DIV/0!. Is there a way I can write that formula so it returns a null or zero in place of that?

I've tried:

{=AVERAGE(IF(E192:E199<>0,E192:E199,0))} - This affects averages where some values are zero and some aren't.

{=AVERAGE(IF(E192:E199<>0,E192:E199,""))} - This has no effect.

Any other suggestions?

 

[GlennUK]

Put an IF around it, like =IF(SUM(range)=0,0,AVERAGE(etc

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[krets]

Ah, perfect. Thanks!