Logical functions
| [[{{{PrevPage}}}|< Previous Page
]] |
[[{{{NextPage}}}|Next Page
>]] |
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
