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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.