Trying to get Excel to agree with Calc on this. booleanlogic.xls
Apologies, not a power user of either.
Row 11 is my target .
To my mind, I was satisfied that Calc followed the boolean logic properly 4>5 is false or 0, 0<6 for a true result in the cell.
This is time-sensitive to me!
Thank you
Both Calc’s TRUE and Excel’s FALSE are correct answers.
Both suites tell you the result of (low>mid)<high, i.e. FALSE<high, as you expect. Different spreadsheet applications follow different models regarding Logical values, e.g. Excel has a dedicated Logical data type that is distinct from number, and in Calc, Logical is just a number (true being equal to 1, and false being 0). For Excel’s model, comparing a Logical value to a number (FALSE<6) gives always false, while Calc calculates 0<6 which is true. It is even documented in standards, e.g. in ODF: see 4.5 Logical (Boolean): “Applications may have a Logical type distinct from both Number and Text, but Logical values may also be represented by the Number type”, and 6.4.9 Infix Operator Ordered Comparison ("<", “<=”, “>”, “>=”): “it is implementation-defined which of these results will be returned when the types (of Left and Right) differ”.
Appreciate the clear explanation. And for not treating me like a nutcase! Is there a way they could agree, i.e., a non-expert user could enter =4>5<6 into Excel and get the same answer as Calc (without involving, say, nested IF’s or such)? Maybe a setting? Thanks
… a non-expert user could enter =4>5<6 into Excel and get the same answer as Calc …
And what would =4>5<6 mean in this context?
“Non expert user” we must accept. User must still be ble to work within the framework of spreadsheet formula syntax.
If you enter a nonsensical formula, you can expect nonsensical result. In that situation, the fact that you get different nonsense from different tools is not relevant. If you abide by syntax, you should get reliable results.
I’m not implying a context. If a set of 3 values arrived by name or reference as arguments in a cell it would be nice to know that both suites would arrive at the same result for that cell. I suppose it is an intellectual exercise, trying to ‘break’ Excel.
It is not.
This difference comes from different “typing conventions” in the two apps. Excel strictly distinguishes between logical (TRUE/FALSE) and numerical data types while Calc does not. This allows for some “shorthand notations” in Calc, at the expense of some integrity checking.
Conversely, Excel calculates with numeric strings more liberally than Calc, which is less likely to reject imported data but may mistake thousands separator for decimal point in some cases. Again, shortcut bypassing integrity check, this time from Excel.
We can wish for the same “spreadsheet standard” to be observed across all apps. Such a standard has not been established. The intention is never (I hope) to “break” the competition. Sometimes random, at other times a deliberate choice. When deliberate, the intention may be ease of use or integrity purpose.
We as users need to understand the peculiarities of our tools. If we must use both Calc and Excel, we also need to know how they differ.
@keme is absolutely right.
Technically, you may manually break your formula into parenthesized parts following operator precedence, and apply N() to each group, like
=N(A>B)<C
But then, irrespective to context as you put it, it is still bound to syntax rules, and so you compare a boolean to some value. This comparison is either nonsensical (if the right-hand value is non-boolean), or falls into boolean operations realm, and there is no “greater than” in that realm (it is defined, of course, for sorting reasons, but has no sense: true is not “greater than” false, they are the opposite of each other). Thus - irrespective of context - this formula is meaningless. It could make sense to check equality/inequality - like “if boolean here is the same as boolean there” (XOR(A>B; NOT(C))) or “if boolean here is the opposite of boolean there” (XOR(A>B; C)). Or, following the “logic” of the formula:
=AND(C; NOT(A>B))“It is not”? If you mean not an intellectual exercise, it most certainly is. Something that expands the understanding of both Excel and Calc. Does it have practical value, not for me to say. But having none would make it an intellectual exercise almost by definition.
“It is not”? If you mean not an intellectual exercise, it most certainly is.
Sorry! I guess i misread the part “… trying to break Excel.” That is what I was referring to, and which I elaborated on.
My bad.
No worries. Didn’t mean reply to come across hot, either.