Difference between revisions of "Documentation/How Tos/Calc: Logical functions"
(→See also) |
(Logical values overview added) |
||
Line 32: | Line 32: | ||
<br style="clear:both;" /> | <br style="clear:both;" /> | ||
<!-- why on earth is this necessary? --> | <!-- why on earth is this necessary? --> | ||
+ | |||
+ | |||
+ | == Logical values overview == | ||
+ | |||
+ | In Calc, logical values are represented by numbers: <tt>'''0'''</tt> is <tt>'''FALSE'''</tt> and <tt>'''1'''</tt> is <tt>'''TRUE'''</tt>. We may enter <tt>'''1'''</tt> in a cell, and then (selecting '''Format - Cells...''') choose the 'Boolean Value' format to display the number as <tt>'''TRUE'''</tt>. | ||
+ | |||
+ | |||
+ | Any Calc function that returns a logical result, actually returns the number <tt>'''0'''</tt> or <tt>'''1'''</tt>. Any Calc <u>cell formula</u> that returns a logical result, tells the cell to display <tt>'''FALSE'''</tt> or <tt>'''TRUE'''</tt> - if the cell has the default (General) format. For example <tt>'''=TRUE()'''</tt> returns the value 1, which is displayed as <tt>'''TRUE'''</tt>; if you then change the format to a numerical format, it displays <tt>'''1'''</tt>. | ||
+ | |||
+ | |||
+ | Any Calc function that tests for a logical result, actually tests if the value evaluates as the number <tt>'''0'''</tt>. <tt>'''0'''</tt> is taken as <tt>'''FALSE'''</tt> and anything else is taken as <tt>'''TRUE'''</tt>. For example where A1 contains <tt>'''apple'''</tt>, <tt>'''NOT(A1)'''</tt> returns <tt>'''TRUE'''</tt> because 'apple' evaluates as <tt>'''0'''</tt> = <tt>'''FALSE'''</tt>. Another example: <tt>'''NOT(57)'''</tt> returns <tt>'''FALSE'''</tt> because <tt>'''57'''</tt> evaluates as <tt>'''TRUE'''</tt>. | ||
+ | |||
+ | |||
+ | Excel in contrast has a separate type for logical values - they are <u>not</u> numbers, but are <u>sometimes</u> converted to numbers. Take care therefore, if you need compatibility: | ||
+ | |||
+ | *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 better <tt>'''SUMPRODUCT((A1:A6=”red”)*(B1:B6=”big”))'''</tt> all work. | ||
Revision as of 04: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”)) or better SUMPRODUCT((A1:A6=”red”)*(B1:B6=”big”)) all work.
See also
Functions listed alphabetically, Functions listed by category