Logical functions
- Logical Functions
- ANDFALSEIF
List of Calc Logical functions
The logical functions operate on logical ('boolean') values, that is, TRUE or FALSE.
AND returns TRUE if all the arguments are TRUE. FALSE returns the logical value FALSE. IF returns one of two values, depending on a test condition. NOT returns TRUE if the argument is FALSE, and FALSE if the argument is TRUE. OR returns TRUE if any of the arguments are TRUE. TRUE returns the logical value TRUE.
Logical values overview
In Calc, logical values are represented by numbers: 0 is FALSE and 1 is TRUE. We may enter 1 in a cell, and then (selecting Format → Cells…) choose the 'Boolean Value' format to display the number as TRUE.
Any Calc function that returns a logical result, actually returns the number 0 or 1. Any Calc cell formula that returns a logical result, tells the cell to display FALSE or TRUE - if the cell has the default (General) format. For example =TRUE() returns the value 1, which is displayed as TRUE; if you then change the format to a numerical format, it displays 1.
Any Calc function that tests for a logical result, actually tests if the value evaluates as the number 0. 0 is taken as FALSE and anything else is taken as TRUE. For example, where A1 contains apple, NOT(A1) returns TRUE because 'apple' evaluates as 0 = FALSE. Another example: NOT(57) returns FALSE because 57 evaluates as TRUE.
Excel in contrast, has a separate type for logical values - they are not numbers, but are sometimes converted to numbers. Take care therefore, if you need compatibility:- In Excel functions like SUM and AVERAGE ignore logical values; in Calc they are numbers and are therefore included. This is unlikely to cause difficulties, because you would not normally expect to try to add up logical values.
- Advanced topic: In Calc SUMPRODUCT(A1:A6="red"; B1:B6="big") counts the number of big red items. In Excel the logical results returned by A1:A6="red" and B1:B6="big" are ignored by SUMPRODUCT; logical values are converted if a number is expected, so SUMPRODUCT(1*(A1:A6="red"); 1*(B1:B6="big")), SUMPRODUCT(0+(A1:A6="red"); 0+(B1:B6="big")), SUMPRODUCT(--(A1:A6="red"); --(B1:B6="big")) or SUMPRODUCT((A1:A6="red")*(B1:B6="big")) all work in both spreadsheets.
See Also
-
