Replacing #DIV/0! by 0

James · May 11, 2004

Hello,

I'm trying to replace error symbols with a 0 in a formula without success !

Please could someone tell me where I'm going wrong in the formula below :

IF(ISNA(SUM(M3:O3)/SUMPRODUCT((M3:O3>0)*1))),0,((SUM(M3:O3)/SUMPRODUCT((M3:O
3>0)*1))))

Thanks very much for your help,

James

JE McGimpsey · May 11, 2004

One way:

=IF(COUNTIF(M3:O3,">0")=0,0,SUM(M3:O3)/COUNTIF(M3:O3,">0"))

James · May 12, 2004

works great. Thanks very much.

James

JE McGimpsey said:
One way:

=IF(COUNTIF(M3:O3,">0")=0,0,SUM(M3:O3)/COUNTIF(M3:O3,">0"))