Difference between revisions of "Documentation/How Tos/Calc: Logical functions"
(→Logical values overview: changed " quotes to allow copy /paste) |
(→Logical values overview) |
||
Line 48: | Line 48: | ||
*In Excel functions like <tt>'''SUM'''</tt> and <tt>'''AVERAGE'''</tt> 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. | *In Excel functions like <tt>'''SUM'''</tt> and <tt>'''AVERAGE'''</tt> 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 <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> or | + | *''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. |
==See also== | ==See also== |
Revision as of 21:06, 4 October 2008
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
Functions listed alphabetically, Functions listed by category