Change divisor if column is 0
 

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.

Change divisor if column is 0
 

Try using =Average(A1:C1)



"Carol Mac" wrote:

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.

Change divisor if column is 0
 

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.

Change divisor if column is 0
 

Maybe one way would be:

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

HTH,
Paul

"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.

Change divisor if column is 0
 

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

"Barb Reinhardt" wrote:

Try using =Average(A1:C1)



"Carol Mac" wrote:

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.

Change divisor if column is 0
 

OK, I missed that. How about this:

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

commit with CTRL SHIRT ENTER.

"Carol Mac" wrote:

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

"Barb Reinhardt" wrote:

Try using =Average(A1:C1)



"Carol Mac" wrote:

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.

Change divisor if column is 0
 

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.

Change divisor if column is 0
 

Try this:

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

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

XL2002, WinXP


"Carol Mac" wrote:

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.

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.

Change divisor if column is 0
 

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" 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.

Change divisor if column is 0
 

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

"Ron Coderre" wrote:

Try this:

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

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

XL2002, WinXP


"Carol Mac" wrote:

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.

Change divisor if column is 0
 

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

"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.

Change divisor if column is 0
 

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


--
Regards,

Peo Sjoblom

"Carol Mac" wrote in message
...
Thanks to all that replied - none of them worked but I appreciate
everyone's
input.

"Ron Coderre" wrote:

Try this:

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

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

XL2002, WinXP


"Carol Mac" wrote:

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.