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

W

WGeorg

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)
 
B

Bob Phillips

Use

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

--

HTH

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

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
 
Top