Apache OpenOffice (AOO) Bugzilla – Issue 84266
=ISNUMBER(FALSE) returns TRUE; logical values converted to numbers
Last modified: 2012-06-04 14:54:18 UTC
Enter =NOT(TRUE) in a cell. The result is FALSE (correct) but the display in the formula bar is =NOT(1). This is a small matter, but anyone trying this would most likely be trying to learn, and would be confused. Excel correctly displays =NOT(TRUE). Same bug in other logical functions: AND, OR, IF
It's worse than I thought: =ISNUMBER(TRUE) displays in the input bar as =ISNUMBER(1), and returns the result TRUE. Similarly =ISLOGICAL(TRUE) returns FALSE. Also =ISNUMBER(A1), where A1 contains TRUE, returns the result TRUE. Excel correctly returns FALSE, because A1 contains a logical value not a number. However =ISLOGICAL(A1) works correctly. As this is now a compatibility issue, I have changed the Summary (was: logical function =NOT(TRUE) displays as =NOT(1) ) and raised priority to P3
Reading the ODFF spec (as at 15Nov07) it seems that this arises because Calc does not have distinct number and logical types. The spec allows a 'level 1' application to return TRUE from ISNUMBER(TRUE) - but the implication is that a level 3 app like Calc should handle this as Excel (if my assumption that level 1 = 'small group' and level 3 = 'large group' is correct - the spec is not clear, at least to me...).
(As I can't find RFE for logical values) Confirming - as described.
Another case where Calc and Excel do not behave the same: cell A1 0 cell A2 =TRUE() AVERAGE(A1:A2) returns 0.5 in Calc and 0 in Excel Excel ignores logical values in AVERAGE by design. Calc cannot, because it has no distinct type.
More information: A1: =TRUE displays 1 - not good A2: =TRUE() displays TRUE ISLOGICAL(A1) displays FALSE - good ISLOGICAL(A2) displays TRUE - good Change the format of A1:A2 to number; both show 1; ISLOGICAL does not change... ..until you hit hard recalculate Shift-Ctrl-F9 - then ->FALSE Change the format of A1:A2 to boolean; both show TRUE; ISLOGICAL does not change... ..until you hit hard recalculate Shift-Ctrl-F9 - then ->TRUE I see the absence of a separate type for logical values as a serious weakness; there are many similar examples. But of course it's a lot of work to remedy.
The problem of not having a boolean data type is already tracked in issue 58572. *** This issue has been marked as a duplicate of issue 58572 ***