Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 08 May 2019 12:18:25 +0200calc logical interpretation of empty cells / arguments 'not intuitive'https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/hello,
with one empty argument for 'AND' and 'OR' you get a result, if all arguments are empty you get '#VALUE!'
(try 'AND(A1;A2)' in A3, any combination of values and even one cell empty in A1:A2 produces a result, two empty cells result in '#VALUE!')
i think that follows the philosophy 'while we find anything evaluateable in the formula we produce a result ignoring unevaluatable overhead,
i have difficulties with that philosophy - and suspect plenty other to have that too while not expressing it,
i'll try to explain:
evaluating 'AND' of '1' and '1' to 'TRUE' is intuitive,
evaluating 'AND' of '0' and '1' to 'FALSE' is intuitive,
evaluating 'AND' of 'empty' and '0' to 'FALSE' produces the idea 'empty' is evaluatable,
evaluating 'AND' of 'empty' and '1' to 'TRUE' 'keeps' the idea that 'empty' is evaluateable? but the result is 'irritating',
evaluating 'AND' of 'empty' and 'empty' to '#VALUE!' shows that 'empty' is not evaluated,
the other samples ignore 'not evaluateable' in one cell while they find something in the other arguments.
imho such behaviour is not 'homogen' and difficult to understand and accept for users, it's similar to 'PRODUCT' of a range ignoring empty cells and not! evaluating and using them as '0'.
imho an empty cell is either something as '0', then it should be evaluated that way in all situations, or it is 'not evaluateable', then formulas should re-act to that in all situations.
the philosophy is not homogenous impemented in calc, '=AND(1;)' (no second argument or 'second argument empty') evaluates to 'FALSE' (second argument evaluated) while '=AND(1;A1)' with A1 - the second argument - being empty or 'no argument' evaluates to 'TRUE' (second argument ignored).
i'd like to hear from others if they like this behaviour, if there are profound reasons to have it like this, or if it's a bug and can be changed,
reg.
b.
P.S. Mike, pls. don't tell me i'm too stupid to understand the complexity neccessary in a program to satisfy your feelings of 'being a digital hero', i'm trying to get 'common intuitive use' and calc together, that's difficult, but imho it's a justified claim. Sat, 04 May 2019 17:22:35 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/Comment by Opaque for <p>hello, </p>
<p>with one empty argument for 'AND' and 'OR' you get a result, if all arguments are empty you get '#VALUE!'</p>
<p>(try 'AND(A1;A2)' in A3, any combination of values and even one cell empty in A1:A2 produces a result, two empty cells result in '#VALUE!') </p>
<p>i think that follows the philosophy 'while we find anything evaluateable in the formula we produce a result ignoring unevaluatable overhead, </p>
<p>i have difficulties with that philosophy - and suspect plenty other to have that too while not expressing it, </p>
<p>i'll try to explain: </p>
<p>evaluating 'AND' of '1' and '1' to 'TRUE' is intuitive, </p>
<p>evaluating 'AND' of '0' and '1' to 'FALSE' is intuitive, </p>
<p>evaluating 'AND' of 'empty' and '0' to 'FALSE' produces the idea 'empty' is evaluatable, </p>
<p>evaluating 'AND' of 'empty' and '1' to 'TRUE' 'keeps' the idea that 'empty' is evaluateable? but the result is 'irritating', </p>
<p>evaluating 'AND' of 'empty' and 'empty' to '#VALUE!' shows that 'empty' is not evaluated, </p>
<p>the other samples ignore 'not evaluateable' in one cell while they find something in the other arguments. </p>
<p>imho such behaviour is not 'homogen' and difficult to understand and accept for users, it's similar to 'PRODUCT' of a range ignoring empty cells and not! evaluating and using them as '0'. </p>
<p>imho an empty cell is either something as '0', then it should be evaluated that way in all situations, or it is 'not evaluateable', then formulas should re-act to that in all situations. </p>
<p>the philosophy is not homogenous impemented in calc, '=AND(1;)' (no second argument or 'second argument empty') evaluates to 'FALSE' (second argument evaluated) while '=AND(1;A1)' with A1 - the second argument - being empty or 'no argument' evaluates to 'TRUE' (second argument ignored). </p>
<p>i'd like to hear from others if they like this behaviour, if there are profound reasons to have it like this, or if it's a bug and can be changed, </p>
<p>reg. </p>
<p>b. </p>
<p>P.S. Mike, pls. don't tell me i'm too stupid to understand the complexity neccessary in a program to satisfy your feelings of 'being a digital hero', i'm trying to get 'common intuitive use' and calc together, that's difficult, but imho it's a justified claim. </p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192640#post-id-192640From my point of view, there is one problem in your description by not to distinct between a numeric value and a boolean value TRUE or FALSE and the fact that AND function works (as per description on functions assistant) on logical values (boolean values), If you format your cells containing any numbers as BOOLEAN, you will see the difference between "empty" = no boolean at all, and 0 (boolean FALSE). In that sense, for me the AND is correct and has no bug. In real situation you use AND arguments like "<cell> [>,<,=] <somevalue>" and this shows the nature of logical expressions used in AND.Sat, 04 May 2019 18:29:31 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192640#post-id-192640Comment by newbie-02 for <p>hello, </p>
<p>with one empty argument for 'AND' and 'OR' you get a result, if all arguments are empty you get '#VALUE!'</p>
<p>(try 'AND(A1;A2)' in A3, any combination of values and even one cell empty in A1:A2 produces a result, two empty cells result in '#VALUE!') </p>
<p>i think that follows the philosophy 'while we find anything evaluateable in the formula we produce a result ignoring unevaluatable overhead, </p>
<p>i have difficulties with that philosophy - and suspect plenty other to have that too while not expressing it, </p>
<p>i'll try to explain: </p>
<p>evaluating 'AND' of '1' and '1' to 'TRUE' is intuitive, </p>
<p>evaluating 'AND' of '0' and '1' to 'FALSE' is intuitive, </p>
<p>evaluating 'AND' of 'empty' and '0' to 'FALSE' produces the idea 'empty' is evaluatable, </p>
<p>evaluating 'AND' of 'empty' and '1' to 'TRUE' 'keeps' the idea that 'empty' is evaluateable? but the result is 'irritating', </p>
<p>evaluating 'AND' of 'empty' and 'empty' to '#VALUE!' shows that 'empty' is not evaluated, </p>
<p>the other samples ignore 'not evaluateable' in one cell while they find something in the other arguments. </p>
<p>imho such behaviour is not 'homogen' and difficult to understand and accept for users, it's similar to 'PRODUCT' of a range ignoring empty cells and not! evaluating and using them as '0'. </p>
<p>imho an empty cell is either something as '0', then it should be evaluated that way in all situations, or it is 'not evaluateable', then formulas should re-act to that in all situations. </p>
<p>the philosophy is not homogenous impemented in calc, '=AND(1;)' (no second argument or 'second argument empty') evaluates to 'FALSE' (second argument evaluated) while '=AND(1;A1)' with A1 - the second argument - being empty or 'no argument' evaluates to 'TRUE' (second argument ignored). </p>
<p>i'd like to hear from others if they like this behaviour, if there are profound reasons to have it like this, or if it's a bug and can be changed, </p>
<p>reg. </p>
<p>b. </p>
<p>P.S. Mike, pls. don't tell me i'm too stupid to understand the complexity neccessary in a program to satisfy your feelings of 'being a digital hero', i'm trying to get 'common intuitive use' and calc together, that's difficult, but imho it's a justified claim. </p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192666#post-id-192666thanks for your comment, imho the functions do not have real 'bugs', but work in a way that conflicts with 'intuitive use'. somewhere - i don't remember where - i'd read that a number '0' is treated as logical false, and anything else as true. the distinction between '0' and 'nothing' is somewhat against me 'feeling' 'nothing', 'empty' and '0' being very similar things, and for me it's difficult to learn and remember plenty special cases, as well as dealing with changed philosophies between "we assign logical 'true' not only to '1' but to any (positive?) value to make things easy and intuitive for the users" against "we treat '0' and 'empty' differently o make things difficult to understand", as well as the 'broken' implementation of interpreting 'empty' differently if the formula contains 'empty' or references to an empty cell.Sun, 05 May 2019 01:03:28 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192666#post-id-192666Comment by Mike Kaganski for <p>hello, </p>
<p>with one empty argument for 'AND' and 'OR' you get a result, if all arguments are empty you get '#VALUE!'</p>
<p>(try 'AND(A1;A2)' in A3, any combination of values and even one cell empty in A1:A2 produces a result, two empty cells result in '#VALUE!') </p>
<p>i think that follows the philosophy 'while we find anything evaluateable in the formula we produce a result ignoring unevaluatable overhead, </p>
<p>i have difficulties with that philosophy - and suspect plenty other to have that too while not expressing it, </p>
<p>i'll try to explain: </p>
<p>evaluating 'AND' of '1' and '1' to 'TRUE' is intuitive, </p>
<p>evaluating 'AND' of '0' and '1' to 'FALSE' is intuitive, </p>
<p>evaluating 'AND' of 'empty' and '0' to 'FALSE' produces the idea 'empty' is evaluatable, </p>
<p>evaluating 'AND' of 'empty' and '1' to 'TRUE' 'keeps' the idea that 'empty' is evaluateable? but the result is 'irritating', </p>
<p>evaluating 'AND' of 'empty' and 'empty' to '#VALUE!' shows that 'empty' is not evaluated, </p>
<p>the other samples ignore 'not evaluateable' in one cell while they find something in the other arguments. </p>
<p>imho such behaviour is not 'homogen' and difficult to understand and accept for users, it's similar to 'PRODUCT' of a range ignoring empty cells and not! evaluating and using them as '0'. </p>
<p>imho an empty cell is either something as '0', then it should be evaluated that way in all situations, or it is 'not evaluateable', then formulas should re-act to that in all situations. </p>
<p>the philosophy is not homogenous impemented in calc, '=AND(1;)' (no second argument or 'second argument empty') evaluates to 'FALSE' (second argument evaluated) while '=AND(1;A1)' with A1 - the second argument - being empty or 'no argument' evaluates to 'TRUE' (second argument ignored). </p>
<p>i'd like to hear from others if they like this behaviour, if there are profound reasons to have it like this, or if it's a bug and can be changed, </p>
<p>reg. </p>
<p>b. </p>
<p>P.S. Mike, pls. don't tell me i'm too stupid to understand the complexity neccessary in a program to satisfy your feelings of 'being a digital hero', i'm trying to get 'common intuitive use' and calc together, that's difficult, but imho it's a justified claim. </p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192685#post-id-192685Reading the description, I agreed with the description of the problem. Reading Lupp's answer, I felt sorry that being part of standard, it's unlikely to change, although I felt that standard's treatment of the matter is questionable (without digging into details).
But reading the last paragraph of the question, I realized that you decided for some reason to attack me personally, [poisoning the well](https://rationalwiki.org/wiki/Poisoning_the_well) using direct lie (claiming me calling someone "too stupid"), and slander (telling that I could tell something "to satisfy my feelings of 'being a digital hero'").
So I won't google for meaning of "digital hero"; instead, I make a note to always check the author of a question n the future; and never ever try to answer and discuss anything with that arrogant person.Sun, 05 May 2019 08:53:13 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192685#post-id-192685Comment by newbie-02 for <p>hello, </p>
<p>with one empty argument for 'AND' and 'OR' you get a result, if all arguments are empty you get '#VALUE!'</p>
<p>(try 'AND(A1;A2)' in A3, any combination of values and even one cell empty in A1:A2 produces a result, two empty cells result in '#VALUE!') </p>
<p>i think that follows the philosophy 'while we find anything evaluateable in the formula we produce a result ignoring unevaluatable overhead, </p>
<p>i have difficulties with that philosophy - and suspect plenty other to have that too while not expressing it, </p>
<p>i'll try to explain: </p>
<p>evaluating 'AND' of '1' and '1' to 'TRUE' is intuitive, </p>
<p>evaluating 'AND' of '0' and '1' to 'FALSE' is intuitive, </p>
<p>evaluating 'AND' of 'empty' and '0' to 'FALSE' produces the idea 'empty' is evaluatable, </p>
<p>evaluating 'AND' of 'empty' and '1' to 'TRUE' 'keeps' the idea that 'empty' is evaluateable? but the result is 'irritating', </p>
<p>evaluating 'AND' of 'empty' and 'empty' to '#VALUE!' shows that 'empty' is not evaluated, </p>
<p>the other samples ignore 'not evaluateable' in one cell while they find something in the other arguments. </p>
<p>imho such behaviour is not 'homogen' and difficult to understand and accept for users, it's similar to 'PRODUCT' of a range ignoring empty cells and not! evaluating and using them as '0'. </p>
<p>imho an empty cell is either something as '0', then it should be evaluated that way in all situations, or it is 'not evaluateable', then formulas should re-act to that in all situations. </p>
<p>the philosophy is not homogenous impemented in calc, '=AND(1;)' (no second argument or 'second argument empty') evaluates to 'FALSE' (second argument evaluated) while '=AND(1;A1)' with A1 - the second argument - being empty or 'no argument' evaluates to 'TRUE' (second argument ignored). </p>
<p>i'd like to hear from others if they like this behaviour, if there are profound reasons to have it like this, or if it's a bug and can be changed, </p>
<p>reg. </p>
<p>b. </p>
<p>P.S. Mike, pls. don't tell me i'm too stupid to understand the complexity neccessary in a program to satisfy your feelings of 'being a digital hero', i'm trying to get 'common intuitive use' and calc together, that's difficult, but imho it's a justified claim. </p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192695#post-id-192695@Mike Kaganski:
thks for agreeing the problem to be something worth to think about,
/ot
you're right, i felt somewhat offended by you in another thread / case (i think it was https://ask.libreoffice.org/en/question/185398/unprecise-switching-evaluation-and-results-when-formatting-a-cell-as-text/), but who told you i'm addressing you with 'Mike'? (k)
may be / consider i'm talking to some other person ... ??? (k)
the conclusion / interpretatíon 'Mike' -> 'Mike Kaganski' is 'homemade', and technical incorrect (at least questionable) as it picks one of 256^9 special cases, ignoring the other ~4.722.366.482.869.650.000.000 (k)
/ot-off
but to keep this forum 'clean' anyone with the 'power' to do it may remove my 'P.S.' as well as Mike Kaganski's comment and this one.
or leave it to give humor some space ... every smile makes a better day
... your decision ...Sun, 05 May 2019 10:19:30 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192695#post-id-192695Answer by Lupp for <p>hello, </p>
<p>with one empty argument for 'AND' and 'OR' you get a result, if all arguments are empty you get '#VALUE!'</p>
<p>(try 'AND(A1;A2)' in A3, any combination of values and even one cell empty in A1:A2 produces a result, two empty cells result in '#VALUE!') </p>
<p>i think that follows the philosophy 'while we find anything evaluateable in the formula we produce a result ignoring unevaluatable overhead, </p>
<p>i have difficulties with that philosophy - and suspect plenty other to have that too while not expressing it, </p>
<p>i'll try to explain: </p>
<p>evaluating 'AND' of '1' and '1' to 'TRUE' is intuitive, </p>
<p>evaluating 'AND' of '0' and '1' to 'FALSE' is intuitive, </p>
<p>evaluating 'AND' of 'empty' and '0' to 'FALSE' produces the idea 'empty' is evaluatable, </p>
<p>evaluating 'AND' of 'empty' and '1' to 'TRUE' 'keeps' the idea that 'empty' is evaluateable? but the result is 'irritating', </p>
<p>evaluating 'AND' of 'empty' and 'empty' to '#VALUE!' shows that 'empty' is not evaluated, </p>
<p>the other samples ignore 'not evaluateable' in one cell while they find something in the other arguments. </p>
<p>imho such behaviour is not 'homogen' and difficult to understand and accept for users, it's similar to 'PRODUCT' of a range ignoring empty cells and not! evaluating and using them as '0'. </p>
<p>imho an empty cell is either something as '0', then it should be evaluated that way in all situations, or it is 'not evaluateable', then formulas should re-act to that in all situations. </p>
<p>the philosophy is not homogenous impemented in calc, '=AND(1;)' (no second argument or 'second argument empty') evaluates to 'FALSE' (second argument evaluated) while '=AND(1;A1)' with A1 - the second argument - being empty or 'no argument' evaluates to 'TRUE' (second argument ignored). </p>
<p>i'd like to hear from others if they like this behaviour, if there are profound reasons to have it like this, or if it's a bug and can be changed, </p>
<p>reg. </p>
<p>b. </p>
<p>P.S. Mike, pls. don't tell me i'm too stupid to understand the complexity neccessary in a program to satisfy your feelings of 'being a digital hero', i'm trying to get 'common intuitive use' and calc together, that's difficult, but imho it's a justified claim. </p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?answer=192641#post-id-192641The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a `LogicalSequence` regarding subchapter 6.3.13 of the [OpenFormula specification](http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html). Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error `#VALUE!` (meaning "Parameter is wrong type." in this case) is returned.Sat, 04 May 2019 18:53:35 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?answer=192641#post-id-192641Comment by newbie-02 for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192691#post-id-192691hey, thanks, good point
enhancing my question:
I suggest a recheck of the logic of 6.3.12 and 6.3.13,
6.3.12: 'If the reference is to an empty cell, consider it FALSE()' - that is 'intuitive' and what users are used to,
6.3.13: 'Empty cells are not included in a LogicalSequence.' is contrary to that logic. and contrary to seeing a LogicalSequence as a 'collection of logicals'.
such 'special cases' hazard errors with 'straying' and changing evaluation 'behind the scene' once size, complexity or syntax of sheet or formulae have grown too far for manual recheck.
e.g. for an simple minded user 'AND(A1:A2)' with A1 and A2 being empty should result in the same as 'AND(SUM(A1:A2))' ... it doesn't. (k)
implementation in calc: while AND(;) (two not given or empty arguments) is! evaluated 'AND()' should be evaluatable too, and also 'AND(A1;A2)' with A1 and A2 being empty.
abstract: 'handling of empty cells deviates between numeric and logical functions' - not good for me.
jm2ctSun, 05 May 2019 09:40:22 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192691#post-id-192691Comment by Lupp for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192706#post-id-192706My ways of thinking about something like addressed with this thread comes from how it's treated generally in mathematics:
Firstly find a context of comparable situations.
The context I found is: "Aggregating functions or functions that are amenable to recursive definition."
Secondly look for specifically similar cases and try to make them similar also concerning aspects seeming arbitrary to the first glance.
I think I'm able to apply the approach in detail.to our case, but I will only post the results in this place:
The specifications of the most important respective functions (converting their parameter to sequences) should result for empty sequences in:
SUM(emptyNumberSequenceList) = 0 (As it actually is.)
PRODUCT(emptyNumberSequenceList) = 1 (As it "wrongly" **not** is. It's a very bad 0.)
AND(emptyLogicalSequence) = TRUE
OR(emptyLogicalSequence) = FALSE
AVERAGE(emptyNumberSequenceList) = error (#VALUE! e.g.)
Additional cases: Ask me specifically.Sun, 05 May 2019 13:00:53 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192706#post-id-192706Comment by Lupp for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192710#post-id-192710@newbie-02:
Your points and examples are valid, IMO. However, the fact that `=AND(;)` evaluates to FALSE is
-1- a bug regarding the given specification.
-2- a bad setting that should not be generalized regarding the reasons I sketched in my above comment.
Summary: Not only can implementations be buggy, but also specifications can be on a meta-level.
That may partly be due to the fact that both are man-made.
But probably even the universe is buggy, though not man-made.
Let's work on enhancements.
In the beginning was the chance and the chance was buggy.Sun, 05 May 2019 13:18:56 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192710#post-id-192710Comment by newbie-02 for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192750#post-id-192750hi @Lupp: i like your approach ... especially trying to keep near to mathematics (as for me a spreadsheet is 'math in practical use') as well as i like the philosophical reflections!!! very good!!!,
and accepting the approach to 'perfect' being hard with a smile gives the chance for many smiles over the day ...
spontaneous comments:
'PRODUCT': 'empty' is very 'near' to '0', thus empty values, cells, references, arguments in - not only - PRODUCT functions should evaluate to '0'* ... '0', thus i'd accept '0',
'=AND(;)': i agree the implementation being buggy acc. to 'the standard', i'd prefer a recheck and possibly change of the questionable standard over a correction of that bug,
but first we have to check which idea stood behind the exclusion of empty cells, may be it's valid and overpowering our thoughts ...
reg.
b.Sun, 05 May 2019 18:23:22 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192750#post-id-192750Comment by newbie-02 for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192787#post-id-192787hi @Lupp: congrats, you're challenging my knowledge and skills ...
on one side i can understand and accept your arguments,
on the other it conflicts with my idea of 'simple intuitive use',
not yet digged to the end ...
you need P(0) to be one for products and recursion, and i think S(0) being zero for the same reason?
eventually assigning different values to the same empty cell for different calculations is 'driving me nuts',
suggestion: calculating a referenced cell is! an argument, and an empty cell represents numerical '0' and logical 'false'. thus products with e.g. 'A1' and A1 being empty should evaluate to '0'. sums should evaluate to 'the rest of the summands', if it's only empty A1 to zero, and logicals to whatever the rest of the function / formula works out with 'false' for the empty cell.
calculating 'no argument given' is a special case and it's results may deviate, thus 'PRODUCT()' may be 1 and SUM() evaluate to 0.
consistent?Mon, 06 May 2019 00:56:32 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192787#post-id-192787Comment by newbie-02 for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192798#post-id-192798one more idea - you see, the case is holding me day and night -
defining P(0) to 1 - it's not calculated but defined - is only? necessary for the generalization of P(k+1) beyond (below) k being 1.
'after' (below) k being 0 the recursion is broken anyway, P(-1) is not defined.
thus P(0) is not really 'needed' - not needed for practical use - but only to have the recursion formula for theoretical mathematics 'nice' using the theoretical value of P(0) to calculate P(1) while P(0) itself needs definition. this can? be changed to defining P(1) and calculating P(n+1)?
i remember from school: 'natural numbers' are defined by '1 is a natural number, each natural number has one successor' starting with 1 and not! with 0 (not! '0 is a natural number ...')
this way we could let PRODUCT(nothing) be 'nothing' - '0' what would also let SUMs(PRODUCTs(nothings)) be '0' instead of something - a little more intuitive.Mon, 06 May 2019 06:44:21 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192798#post-id-192798Comment by newbie-02 for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192802#post-id-192802to come back to my initial question, and boiling it down ...
1. SUM(empty cell) is! calculated to 0,
2. PRODUCT(empty cell) is! calculated to 0,
3. AND(SUM(empty cell)) is! calculated to FALSE,
4. OR(PRODUCT(empty cell)) is! calculated to FALSE,
5. even TEXT(empty cell;format string) is evaluated,
6. why the heck do AND(empty cell) and OR(empty cell) die in #VALUE! ?
imho: 'irritating'
7. why the heck is AND(some value;empty cell) calculated to TRUE ?
imho: 'error'
8. (7.) is not 'logically explainable' while AND(SUM(some value);SUM(empty cell)) results in FALSE !
essence: 'interpretation / handling of empty cells deviates between numerical and logical functions' imho that's worth filing a bug.Mon, 06 May 2019 07:28:53 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192802#post-id-192802Comment by Lupp for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192839#post-id-192839Well, I teached (first as an amateur, then as a professional) math, physics, and in some specific variants a bit of programming for nearly 60 years.
From my point of view I had to stress -as far as the topics were concerned- correctness and the consistent fitting into a context.
Part of the pedagogic objectives then was to help students to understand the reasons for what was "fact", and to see in what sense it was intuitive -probably on a second level.
In some cases it was not possible to get a kind of congruence with what was supposed to be intuitive before the thorough analysis.
Once again: Your "2." tells how it currently is in LibO Calc. I don't know the reasons. The specification tells (in an unclear way) it should be an error, and allows for an unspecified alternative. I sketched the argument for what math tells us. Math is better than spreadsheet sloppiness.Mon, 06 May 2019 12:35:13 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192839#post-id-192839Comment by Lupp for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192844#post-id-192844(Continuerd)
"3." Misses the situation completely: `SUM(ReferenceToBlankCell)` is (doubtably, but factually) simply `0`, and numeric zero is specified to be interpreted as a logical `FALSE` if conversion is needed..
`AND(FALSE) = FALSE` is correct then, and is nothing to do with the empty-sequence-question.
"4." Same thing because `PRODUCT(EmptySequence)` comes out (wrongly!) as `0`, and
`OR(FALSE)=FALSE`. The consistent `PRODUCT(EmptySequence)=1` would result in the also consistent result TRUE for the OR() expression.
"5.": Yes. That's really bad, but it isn't actually related to our topic here. It is to do with a chunk of terrible inconsistencies once introduced to spreadsheets, and now no longer fightable due to established custom. (The treatment of blank cells is basically rotten in spreadsheets. It even disregards the transitivity of the equality relation.)
"6." Bad. Already discussed.
"7." No error if `some value<>0`,
Generally: No cognition without irritation!Mon, 06 May 2019 12:57:52 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192844#post-id-192844Comment by Lupp for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192846#post-id-192846(Continued)
"8." Erroneous. Easily explainable based on the specifications on automatic conversion
If automatic conversion itself is a good idea may well be debated contentiously on the other hand.Mon, 06 May 2019 13:06:12 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192846#post-id-192846Comment by newbie-02 for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192909#post-id-192909@Lupp: MANY MANY THANKS, you've given me much more than a smile!
60 years of experience ... i'll not! go into the ring ;-)
1. to 7. are not criticism, just observations how calc works just now.
i can 'like' 1. to 5. and accept you don't agree,
6. and 7. are against my intuition,
while we do! have inconsistency iyho, and violations of 'true mathematics', eventually it's an enhancement to design something a little better though not being perfect?
can you agree to 'the handling of empty cells should at least be consistent between all of the functions applied in one system, even if it's not! perfect'?
can you agree to 'as the treatment of blank cells is "basically rotten in spreadsheets", it would be an enhancement to design it a little more consistent, and a little more usable for simple minded users'?
philosophical: can you agree to 'nevertheless religion is taught by priests, it must please the people.'?
consider me more part of 'people' than priest ;-) ...Tue, 07 May 2019 04:54:42 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192909#post-id-192909Comment by newbie-02 for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192911#post-id-192911as we just talked about automatic conversion ...
'1. and 7. ...' in the above comment was written and meant as '6. and 7. ...', but automatically converted :-(Tue, 07 May 2019 05:02:10 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192911#post-id-192911Comment by Lupp for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192943#post-id-192943This discussion has long gone beyond what fits into this site's formats. Just one more comment ;-)
If someone agrees with my statement about the handling of blank cells in spreadsheets being "basically rotten", he (f/m) shouldn't demand to force a new consitency by making everything else rotten, too.
(And: You won't get me to accept `PRODUCT(EmptySequence)=0` as appropriate due to being "intuitive". It is basically incompatible with the well justified setting of `a^0=1` globally teached as math. Of course, I might choose to cease using spreadsheets. They're rarely enough of signifacant use since dominated by the idea of having to be compatible with Excel V whichone.)
If you are interested in an additional argument, please write to jag ::at:: psilosoph+de.Tue, 07 May 2019 11:47:23 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192943#post-id-192943Comment by newbie-02 for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=193042#post-id-193042@Lupp: whow, you got me / got it, 'f(empty)' needs to be the neutral element for 'f'. thus PRODUCT(nothing)=1 is ok. sorry, i was slow.
questions left:
what is the neutral element for a logical function / operation?
is the reference to a blank cell something like 'having no operand / argument' or more something like 'the argument / operand is something associateable with nothing/no worth/zero'?
while writing i get the idea you'll know and teach me something about the distinction between functions and operations ... sorry again, at this point i miss at least 40 years of 'practising math'.
i'm quite stretched, threw your ideas in a discussion last night and was 'thrown back' with:
- lambda Kalkül
- recursion
- y-Kombinator
- Kategorie-Theorie
- Hilberts Hotel
- aleph Abstufungen
- Brady - youtube
- computerphile
- numberphile
- 3blue1brown
and just now 'math logic is broken anyway': infinite + infinite = infinite -> infinite - infinite = infinite???
i'll mail ...Wed, 08 May 2019 07:08:38 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=193042#post-id-193042Comment by Lupp for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=193072#post-id-193072There is a well established habit in Boolean Algebra to write disjunction (OR, better adjunction?) with the `+` as the operator an conjunction (AND) with `*`. This is corresponding with my suggestion above to specify `TRUE` as the value to be returned by `AND(EmptySequence)` and `FALSE` for `OR(EmptySequence)`.
Apart from formal arguing relying on recursion ar analogies you may "convince" yourself of `AND(EmptySequence)=TRUE` being "intuitive" by the translation of "All the elements need to be `TRUE`." to "All the elements **must not be** `FALSE`." An empty seuence cannot contain a `FALSE` element after all.Wed, 08 May 2019 11:53:30 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=193072#post-id-193072Comment by Lupp for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=193075#post-id-193075Concerning your slightly smiling remarks about broken math logic I need to admit that I'm not sufficiently familiar with English terminology insofar. Doing away with the smile I would state:
Treatment of "uneigentliche Elemente" and allowing for "Ausartungsfälle" are related topics, but also distinctly distinct. This in the sense that introducing "uneigentliche Elemente" some distinctions based on pragmatic judgements can more easily be accepted.
Well known example: Adding "uneigentliche Elemente" to the field of real numbers generally is done by introducing two of them: {+∞, -∞}. Having embedded the real numbers into the field of complex numbers this doesn't make sense any longer. For many purposes you will accept only one infinite element, and subjoin {∞}. If you want to do alanlytical geometry using complex numbers, you will need an infinite set of "uneigentliche Elemente" to get a model for the classes of parallel lines. ...Wed, 08 May 2019 12:06:43 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=193075#post-id-193075Comment by Lupp for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=193077#post-id-193077And you may develop a non-standard theory of "calculus" treating the infinitely small elements you then need to allow for as "uneigentliche reelle Zahlen". The set of "uneigentliche Elemente" then is of higher cardinality by an infinite order than the "eigentliche" numbers.
You may also still doubt whether it was a **final** decision in history of math to accept the concept of "actual infinity" which was formerly rejected by many. ("Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können." D. Hilbert. That doesn't sound math.) Anyway the critical problems of modern math (related to undecidability) seem to not be introduced by that decision.Wed, 08 May 2019 12:18:25 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=193077#post-id-193077Comment by Lupp for <p>The logical functions AND() and OR() are not specified for exactly two parameters of a specific type, but for an arbitrary number of parameters, each being treated by conversion to a <code>LogicalSequence</code> regarding subchapter 6.3.13 of the <a href="http://docs.oasis-open.org/office/v1.2/os/OpenDocument-v1.2-os-part2.html">OpenFormula specification</a>. Since text contents and blank cells are specified as to be ignored, the process results in an empty sequence if none of the parameter positions contains anything convertible to logical under the given rules. For an empty parameter sequence no result is specified. Thus the error <code>#VALUE!</code> (meaning "Parameter is wrong type." in this case) is returned.</p>
https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192761#post-id-192761Concerning your remark on PRODUCT and its (the remark's) generalization I would severely object.
A sequence `P(k)` created by acumulated multiplication of n factors `f(j)` (j = 1 through n) has a recursion formula analogous to what a series (members of a sequence of summands added one by one) has.
`P(k+1) = P(k) * f(k+1)`.
Generalizing this to the possible extreme where a product with zero factors comes in sight
`P(1) = P(0) * f(1)` should hold. This requires to set `P(0) = 1`. For basically the same reason the sequential logical conjunction needs to start with TRUE in case of zero members.
Otherwise you would break recursion without urgent need.
You surely remember that "higher" mathematics sets `a^0 = 1`, at least for `a <> 0` for exactly this reason.
`0^0` is let undefined fundamentally for additional reasons, but in practical treatment of power series even `0^0=1` is accepted.Sun, 05 May 2019 19:26:16 +0200https://ask.libreoffice.org/en/question/192636/calc-logical-interpretation-of-empty-cells-arguments-not-intuitive/?comment=192761#post-id-192761