Change divisor if column is 0

[Carol Mac]

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.

 

[Barb Reinhardt]

Try using =Average(A1:C1)

 

[Peo Sjoblom]

Maybe

=SUM(A1:C1)/COUNTIF(A1:C1,">0")

 

[PCLIVE]

Maybe one way would be:

=(A1+B1+C1)/COUNTIF(A1:C1,">0")

HTH,
Paul

 

[Carol Mac]

Thanks Barb - doesn't do it. If A1 is 0, it still averages based on 3
instead of 2. Any other thoughts?

 

[Barb Reinhardt]

OK, I missed that. How about this:

=AVERAGE(IF(A1:C1<>0,A1:C1))

commit with CTRL SHIRT ENTER.

 

[Carol Mac]

Thanks Peo & Paul - this gives me a #DIV/0! error. I also tried COUNT,
COUNTBLANK and COUNTA. None worked.

 

[Ron Coderre]

Try this:

=IF(COUNTIF(A1:C1,">0"),SUM(A1:C1)/COUNTIF(A1:C1,">0"),0)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Peo Sjoblom]

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

 

[PCLIVE]

Two ways:

=IF(SUM(A1:C1)>0,SUM(A1:C1)/COUNTIF(A1:C1,">0"),0)

=IF(SUM(A1:C1)=0,"",SUM(A1:C1)/SUMPRODUCT(--(A1:C1<>""),--(A1:C1<>0)))


Good luck,
Paul

 

[Carol Mac]

Thanks to all that replied - none of them worked but I appreciate everyone's
input.

 

[Carol Mac]

This worked - Iwhen I first tried it I entered the wrong cells - DUH. Thanks
again to all.

 

[Peo Sjoblom]

All should work, this is not rocket science, in fact this problem should be
easy to solve. Can you be more specific what does not work and when it does
not work what the contents of those cells are? It might be that what you
think are numbers are text. What do you get if you use

=COUNTA(A1:C1)-COUNT(A1:C1)=0

if you get FALSE you have text in the cells