Difference between revisions of "Documentation/How Tos/Calc: Logical functions"
(→Logical values overview) |
m (→See also) |
||
Line 50: | Line 50: | ||
*''Advanced topic'': In Calc <tt>'''SUMPRODUCT(A1:A6="red"; B1:B6="big")'''</tt> counts the number of big red items. In Excel the logical results returned by <tt>'''A1:A6="red"'''</tt> and <tt>'''B1:B6="big"'''</tt> are ignored by <tt>'''SUMPRODUCT'''</tt>; logical values are converted if a number is expected, so <tt>'''SUMPRODUCT(1*(A1:A6="red"); 1*(B1:B6="big"))'''</tt>, <tt>'''SUMPRODUCT(0+(A1:A6="red"); 0+(B1:B6="big"))'''</tt>, <tt>'''SUMPRODUCT(--(A1:A6="red"); --(B1:B6="big"))'''</tt> or <tt>'''SUMPRODUCT((A1:A6="red")*(B1:B6="big"))'''</tt> all work in both spreadsheets. | *''Advanced topic'': In Calc <tt>'''SUMPRODUCT(A1:A6="red"; B1:B6="big")'''</tt> counts the number of big red items. In Excel the logical results returned by <tt>'''A1:A6="red"'''</tt> and <tt>'''B1:B6="big"'''</tt> are ignored by <tt>'''SUMPRODUCT'''</tt>; logical values are converted if a number is expected, so <tt>'''SUMPRODUCT(1*(A1:A6="red"); 1*(B1:B6="big"))'''</tt>, <tt>'''SUMPRODUCT(0+(A1:A6="red"); 0+(B1:B6="big"))'''</tt>, <tt>'''SUMPRODUCT(--(A1:A6="red"); --(B1:B6="big"))'''</tt> or <tt>'''SUMPRODUCT((A1:A6="red")*(B1:B6="big"))'''</tt> all work in both spreadsheets. | ||
− | + | {{Documentation/SeeAlso| | |
− | + | * [[Documentation/How_Tos/Calc: Functions listed alphabetically|Functions listed alphabetically]], | |
− | [[Documentation/How_Tos/Calc: Functions listed alphabetically| | + | * [[Documentation/How_Tos/Calc: Functions listed by category|Functions listed by category]] |
− | [[Documentation/How_Tos/Calc: Functions listed by category| | + | }} |
Revision as of 09:18, 3 March 2009
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.