ARG! Pull my hair out. Null fields and formulas

B

BrianC

I have Excel 2002 SP3 and two spreadsheets. The following formula i
spreadsheet #1:

=A1*5, when A1 has the formula ="" in it returns 0 (behaving a
expected)

in spreadsheet #2 the same exact formula and assignments returns th
'#VALUE' error message.

I thought that null values are suppose to calc as zeros. It does i
one spreadsheet but doesnt in the other.

Any ideas on why? All cell formats and 'Tools' - 'Options' setting
are identical
 
A

Aladin Akyurek

Try:

=N(A1)*5
I have Excel 2002 SP3 and two spreadsheets. The following formula i
spreadsheet #1:

=A1*5, when A1 has the formula ="" in it returns 0 (behaving a
expected)

in spreadsheet #2 the same exact formula and assignments returns th
'#VALUE' error message.

I thought that null values are suppose to calc as zeros. It does i
one spreadsheet but doesnt in the other.

Any ideas on why? All cell formats and 'Tools' - 'Options' setting
are identical
Click to expand...
 
D

Dave Peterson

One of your workbooks has a Lotus transition setting turned on. (I think you
missed a setting.)

Tools|options|transition tab|
The workbook that returns 0 has: transition formula evaluation checked.
the workbook that returns #value! has it unchecked.

For what it's worth, most excel users have this unchecked and would get the
#value! error.
 
Top