Version: 7.5.9.2 (X86_64) / LibreOffice Community
Build ID: cdeefe45c17511d326101eed8008ac4092f278a9
CPU threads: 4; OS: Windows 10.0 Build 19045; UI render: Skia/Raster; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: threaded
When CALC starts it defaults to the cell format - Number;General - and will interpret text entered into any cell as genuine searchable text.
If the words true or false are entered it interprets those as TRUE or FALSE unless they are preceded with the apostrophe " ’ " or any other text formatting symbol.
If the cells are pre-defined as text then the same entries are interpreted as text.
If the Auto-Boolean cells are subsequently reformated as text then their contents become 1 & 0
Is any of that a bug?
Is there a setting to ensure the text is interpreted as text unless preceded with the formula symbol “=”?
Yes, as mentioned by somebody:
But if you search for a central configuration switch, the answer will be no. You may create your own default template for your needs.
The ability to have mire than one template with different settings is actually a feature wich I prefer instead of having (lots of) central config buttons.
Try to reformat a date or time as number. As with boolean this will reveal internal representation. Note: Format will not change a value, it only alters what is shown.
Are you now asking about any text or just the strings “TRUE” and “FALSE”?
You can very simply explain to Calc that these strings should not be transformed in any way.
Change to any locale where the words “TRUE” and “FALSE” do not mean anything specific. For example, in Bulgarian it will be “ВЯРНО/НЕВЯРНО”, in Dutch “WAAR/ONWAAR”, in Greek “ΣΩΣΤΌ/ΛΆΘΟΣ” - and it is these words that you are unlikely to type on the keyboard that will convert to 1 or 0.
But pressing an apostrophe is much easier
Any idea of what is the value to the user in turning the text into boolean in a random cell? What can it be used for? I can imagine the query where A1 contains TRUE A2 contains FALSE A3 formula If(A1=“TRUE”;A2;“CRAASH”) - real chocolate teapot territory
I discovered it with a simple VLOOKUP() in A3 where the word “false” is contained in the target table alongside 7000+ other words and I tried to enter “false” into the search source A3.
Also populate two adjacent cells with true and false then cut and paste special - Values only and whereas any other formula containing text is copied as the text this will still produce 1 & 0 - is there a bug there?
… or, if the goal is to make all cells textual by default, the template mentioned by @Wanderer is easily created by changing its default cell style’s number format to Text.
The same value, and the same goal, as in turning a random text “123” into a number, which is one hundred, two tens, and three. Calc is a spreadsheet processor, which is designed for multiple kinds of primarily numerical data; booleans are one kind of that. People need it in very different areas of computational tasks.
Note that e.g. the specific of TRUE = 1, and FALSE = 0, allows e.g. using SUMPRODUCT multiplying something by the booleans, to only take those into account, where a property is TRUE.
Hi John, Also easier to forget unless we enter into a loong discussion over the merits of needing to identify that text is text unless it is preeeded by the formula directive “=”.
I still dont see any benefit to a user of a random cell become a boolean with no apparent precedence or dependency.
Hi Mike, But the booleans are associated with a process of some description. Random TRUE or FALSE in unrelated cells surely can’t be of any use to a formula elsewhere in the sheet.
What specifically do you mean by that? Calc has no “prophecy” mode, to know that the user won’t use this “random” cell in a formula the next minute.
How would a cell containing just TRUE or FALSE assist a formula elsewhere? If the formula requires a boolean assessment then it would be contained in the formula not some kind of test to get the formula to go to A1 or A2 to DISCOVER whether the result is TRUE or FALSE
One example: I have a complex calculation of work time, which involves optionally taking into account national holidays, or not. Some sheet has the tabular data; and on another sheet, there are “constants”, like “use this date range”; and one of this constants is “use national holiday table?” - TRUE/FALSE. The formulas of the tabular data would take that fixed cell into account, when using NETWORKDAYS.
Are you talking about the formula you gave an example of above?
Don’t write A1="TRUE", just write =IF(A1;A2;"CRAASH")
So you set a boolean flag in say A1 and then when you process the sheet you decide whether you’re using national holidays A1 is then edited with True or False and the output will change acording to the value. Isn’t that just the same as IF()?
Don’t misunderstand, not arguing - trying to understand
But then A1 could contain “Anything” and still return TRUE so I still don’t understand the justification of auto-bololean-
aslong a cell is NOT preformatted as Text OR input is prefixed with ', many kinds of Input is INTERPRETET as something:
12:00 is interpretet as clock-time | duration with value 0.5
2 5/8 is interpretet as mixed fraction with value 2.625
10 % is interpretet as percent with value 0.1
…
and true or false is interpretet as boolean
there is nothing special about the last, so NO reason to complain!
Unlike Excel, Calc does not have a Boolean cell value type.
When you enter True (localized), the cell value is assigned the number 1, when you enter False, the number is 0.
And what is the value of text in an arithmetic program? What is wrong with boolean in respect to your calculation model?
All spreadsheets since Visicalc of 1979 assume numeric context in all the cells, except for the cells that are explicitly marked as labels. Labels describe the meaning of numeric values. Regarding boolean, Excel introduced boolean as a third data type for input “true” and “false”. In Calc, boolean values are just number formats for zero and non-zero.
How to flag a cell as text in Excel and Calc? Apply number format code “@” (literal text) before editing. This will also allow for literal text with leading equal signs.
Alternatively, you can start input into a regular numeric cell with an apostrophe. The leading apostrophe won’t be part of the cell value.
I understand the interpretation of “Real World Values” and they are indeed useful but I still don’t see the value of changing two words from 200000+ in the English language and making them something they are not - especially as there seems to be no real argument for just having an isolated Boolean value in an unreferenced cell. like you I’m happy to see intelligent inference with 12/12/12 being interpreted as a date and 05:25:15 as a time but where is it helpful to take the word “False” being used in a structured array lookup and inventing a Boolean operator - try entering the text “it’s a false premise” in any cell and then doing a DATA>Text to Columns and see what you get - that same old chocolate teapot
@Villeroy Calc offers many text functions in an an arithmetic program.
Perhaps try typing “it’s a false premise” into any cell and then MENU>Text to Columns and observe the results. Genius
I cannot test if removing the “Double Unary Operator” (apparently required to turn TRUE into 1 and FALSE into 0 in Excel) in the formulas in this link (How to Use SUMPRODUCT with Criteria in Excel (5 Methods) ) will cause the formula to fail in Excel, but it seems to cause confusion in the page itself. This is just one of several different pages on Excel that I found that added the -- in SUMPRODUCT; LibreOffice doesn’t need it.
BTW in Text Import or Text to Columns it is for the user to specify Column type
@EarnestAl Ah, yes, Text to columns does indeed allow the control (Thanks, I’ve learned something new today) - but I still don’t understand why converting False and True is auto-boolean when there are literally millions of Phrases in English where False and True make sense but only one in CALC which is IMPOSED upon everybody. Perhaps a statistician can think of a few examples of good practice for TRUE and FALSE but a Logophile can provide a few more solid examples.