Change divisor if column is 0

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

OK, I missed that. How about this:

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

commit with CTRL SHIRT ENTER.

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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