Product: Spreadsheet
Type: changed
Title: Reference to empty cell not forced to type
Posted by: [EMAIL PROTECTED]
Affected: sc
Effective from: cws odff
*Flags*
-------
API/ BASIC [ ]
Configuration [ ]
File format change [ ]
Help/ Guide [x]
Performance test [ ]
Translation [ ]
UI relevant [ ]
*Description*
-------------
Previously, empty cells were forced to numeric 0 in some contexts and to
empty string in others, except in direct comparison where =A1=0 and
=A1="" both resulted in TRUE if A1 was empty. Emptiness now is inherited
until used, so both =VLOOKUP(...)=0 and =VLOOKUP(...)="" give TRUE if
the lookup resulted in an empty cell being returned. A simple reference
to an empty cell is still displayed as numeric 0 but is not necessarily
of type numeric anymore, so also comparisons with the referencing cell
work as expected. For example:
A1: 1 B1: <empty> C1: =B1 (displays 0)
=B1=0 => TRUE
=B1="" => TRUE
=C1=0 => TRUE
=C1="" => TRUE (previously was FALSE)
=ISNUMBER(B1) => FALSE
=ISNUMBER(C1) => FALSE (previously was TRUE)
=ISNUMBER(VLOOKUP(1;A1:C1;2)) => FALSE (B1)
=ISNUMBER(VLOOKUP(1;A1:C1;3)) => FALSE (C1, previously was TRUE)
=ISTEXT(B1) => FALSE
=ISTEXT(C1) => FALSE
=ISTEXT(VLOOKUP(1;A1:C1;2)) => FALSE (B1, previously was TRUE)
=ISTEXT(VLOOKUP(1;A1:C1;3)) => FALSE (C1)
=ISBLANK(B1) => TRUE
=ISBLANK(C1) => FALSE
=ISBLANK(VLOOKUP(1;A1:C1;2)) => TRUE (B1, previously was FALSE)
=ISBLANK(VLOOKUP(1;A1:C1;3)) => FALSE (C1)
Note that MS-Excel has a difference in inherited emptiness and treats
the result of a reference to an empty cell or a formula cell with the
result of an empty cell, such as VLOOKUP(...) returning an empty cell,
always as number. For example, Calc vs. Excel:
A1: <empty>
B1: =A1 => displays 0, but is just a reference to empty
=ISNUMBER(A1) => FALSE
=ISTEXT(A1) => FALSE
=A1=0 => TRUE
=A1="" => TRUE
=ISNUMBER(B1) => FALSE (MS-Excel: TRUE)
=ISTEXT(B1) => FALSE
=B1=0 => TRUE
=B1="" => TRUE (MS-Excel: FALSE)
C1: =VLOOKUP(...) with empty cell result
=> displays empty (MS-Excel: displays 0)
=ISNUMBER(VLOOKUP(...)) => FALSE
=ISTEXT(VLOOKUP(...)) => FALSE
=ISNUMBER(C1) => FALSE (MS-Excel: TRUE)
=ISTEXT(C1) => FALSE
Send feedback to [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
