Change divisor if column is 0
If you get a DIV error then all three cells must be empty or negative
meaning the only way is that if the countif part returns zero and that means
no values in A1:C1 are greater than zero, if that's the case
=IF(COUNTIF(A1:C1,"0")=0,0,SUM(A1:C1)/COUNTIF(A1:C1,"0"))
will return zero if that's the case, if you have negative values and you
just want to
check for empty cells
=IF(COUNT(A1:C1)=0,0,SUM(A1:C1)/COUNTIF(A1:C1,"<"))
Another way
=IF(COUNT(A1:C1)=0,0,AVERAGE(IF(A1:C1<"",A1:C1)))
entered with ctrl + shift & enter
--
Regards,
Peo Sjoblom
"Carol Mac" wrote in message
...
Thanks Peo & Paul - this gives me a #DIV/0! error. I also tried COUNT,
COUNTBLANK and COUNTA. None worked.
"Peo Sjoblom" wrote:
Maybe
=SUM(A1:C1)/COUNTIF(A1:C1,"0")
--
Regards,
Peo Sjoblom
"Carol Mac" wrote in message
...
D1=(A1+B1+C1)/3
If A1, B1 or C1 have a 0 value, can the divisor change automatically so
that
it only divides by the number of columns with a value? Thanks.
|