if A1=Null and B1=A1 why is result in B1=0 ??

[Guest]

if A1=Null and B1=A1 why is result in B1=0 ??
because of this I get wrong result for average calculations: average for
(6,8,0) <> average for (6,8,null)

 

[Bob Phillips]

Use

=IF(A1="","",A1)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[CLR]

Use this in B1.........

=IF(OR(A1=0,A1=""),"",A1)

Vaya con Dios,
Chuck, CABGx3

 

[Jerry W. Lewis]

A formula must return something. Since Excel has no null value
(long-standing complaint) that the formula can return, it is coerced to
zero. As others have noted, you can use an =IF() formula to handle this
situation and "" will look blank and be ignored by range functions such
as AVERAGE(), etc. Unfortunately it will create an error with binary
operators such as +-*/ and will plot as zero. #N/A or equivalently NA()
will not plot, but will be propagated through formulas. Many answers,
depending on what you want to do with it.

Jerry