Difference between revisions of "Documentation/How Tos/Calc: Logical functions"
From Apache OpenOffice Wiki
< Documentation | How Tos
m |
|||
| Line 1: | Line 1: | ||
| − | {{ | + | {{DISPLAYTITLE:Logical functions}} |
| − | + | {{Documentation/CalcFunc LogicalTOC | |
| − | + | |ShowPrevNext=block | |
| − | + | |PrevPage=Documentation/How_Tos/Calc:_TYPE_function | |
| − | | | + | |NextPage=Documentation/How_Tos/Calc:_AND_function |
| − | + | ||
| − | | | + | |
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
| − | + | ||
}}__NOTOC__ | }}__NOTOC__ | ||
| + | [[File:open.jpg]] | ||
==List of Calc Logical functions== | ==List of Calc Logical functions== | ||
| Line 54: | Line 44: | ||
== Logical values overview == | == 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 | + | 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 {{menu|Format|Cells…}}) choose the 'Boolean Value' format to display the number as <tt>'''TRUE'''</tt>. |
| Line 60: | Line 50: | ||
| − | 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>. | + | 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: | + | 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. | *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. | ||
Revision as of 13:39, 31 January 2024
- 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
-
