=SUMPRODUCT({TRUE};1)
Result is 1 in Calc and 0 in Excel.
You had been given some examples and an explanation. The industry standard in the field of spreadsheets is use of these two words as keywords for boolean values. The industry standard is also to try to convert everything that user enters as a source for either number or formula (by default). The opinion of logophiles is indeed valuable, but in some other contexts. Thank you for the interesting abstract discussion.
1 Like
If you reveal all the sigma functions and just type their name without parameters in any cell itâs just treated as text and I will assume itâs the same for all CALC functions. If you type true or false it becomes a function without any parameters.
What I donât understand is how these parameterless functions can be useful in isolated cells.
You did indeed provide some examples but they presumably utilised TRUE or FALSE within their cell formulae and not requiring references to external cells simply loaded with TRUE or FALSE.
Are you implying you write a formula calling either TRUE or FALSE according to some test -in that formula - from an external cell identified by that test and then use the TRUE or FALSE logic on parameters contained within that formula (or external calls)?
It may be industry standard but I once worked in a company who imported a CEO from outside and he called us all together and explained how we were all going to adopt a new procedure. When I asked him why he replied âBecause we used to do it at Hewlett Packardâ. I responded âIt doesnât matter where you may once have performed that task, what Iâm asking is how does that benefit us or our customersâ. He didnât know the answer - and was replaced about 6 weeks later.
In Calc, boolean values are just number formats for zero and non-zero.
and if they are simply present in a cell with no parameters then there is something in each of those cells that is of numeric or non numeric value - so doesnât that imply they are both true
Itâs using wrong tool for the job. Itâs your task to check if Calc fits to you (or your customers). But itâs not like âit doesnât matter what Calc did beforeâ, because it indeed does for millions of users; and if you canât live with that, you will leave (this site) 6 weeks later.
Not everything that Calc does is to benefit you personally. You persist in claiming that if you canât see the benefit, thereâs none (for anyone else). Itâs not a correct approach, and you donât face your employees here, who are obliged to clarify it ad nauseam.
Do these words have the same sense in English as Yes and No, On and Off, Open and Closed? Truth and Untruth? Or do logophiles not know synonyms to replace reserved words?
I think you misunderstood the analogy. Iâm looking for an explanation of how a parameterless function is utilised. I donât understand how looking at a cell that says true or false is incorporated into a formula any better than an if;then;else You gave an example of when you might use it but no sample of the structure or how it works. It was much better than âwe used to do itâŚâ but unfortunately provided little insight into how that remote cell containing true or false is generated or selected. Is it more adaptable/accurate/efficient than IF x do y else do z? Telling me that everybody does it because itâs there is not the same as explaining what the benefits are to somebody who only ever encounters the TRUE/FALSE scenario when using functions like VLOOKUP() and defining whether the requirement is for an exact or a fuzzy match. The validity is assessed by the calling function defined by the usersâ parameters within that function, not reference to range A1:A2 containing TRUE or FALSE - now lookup using A1 or A2.
I said explicitly: that was one of the constants. I.e., a user simply types âtrueâ there. You just expect every such cell to be generated by an automation; no it is not. Today I want to count with this set; I type this value, and see the result. I type another, and have another set of results. Itâs a kind of manual âwhat ifâ. And that was just a simple example that came to my mind.
So its interrogative - I might say âwhat month do you desireâ and the answer is Sep or 9 and you would say âDo you want Sepâ and the response is TRUE
I suggest returning to the starting question.
When you enter a value into a cell, the spreadsheet program (Calc, Excel) tries to âguessâ your intent and minimize your effort.
Which of the 2 options that have the same result in Calc is more convenient for the user entering data into a cell of a general format?
- Enter a value
1. Open the cell format dialog and select the âBoolean Valueâ format. - Enter
true.
1 Like
Good idea.
Iâm now beginning to understand and I think it depends upon the experience of the person entering the data.
I have previously used mechanisms where I just expect any character entered into the âcheck-boxâ with the test if(A1="";action1;action2) and I now understand I can just use If(A1;action1;action2).
I feel expecting âtrueâ or âfalseâ is a little âlong-windedâ and that letting the mere presence of data in the cell be the trigger for further action is perhaps âfriendlierâ.
My approach has been successful until I just happened to search for the word âFalseâ in an array of 7000 entries which kept disappearing - it was never going to find 0 so both the trigger and target were seemingly (impossibly) null. Good old IFERROR() prevented anything remotely resembling a warning.
It transpires that my cell fill was exactly the same colour as the auto colour when Calc chooses to represent text and results in two predefined colours - setting FALSE to result format instead of text.
The original data was imported as a text file and happened to be placed into text formatted cells.
The enquiry was written on a different sheet which had the Calc default cell format.