calc logical interpretation of empty cells / arguments 'not intuitive'

English
, , , , ,
#1

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.

#2

From 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 " [>,<,=] " and this shows the nature of logical expressions used in AND.

#3

thanks 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.

#4

Reading 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 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.

#5

@mikekaganski:

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 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 …

#6

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 LogicalSequence regarding subchapter 6.3.13 of the OpenFormula specification. 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.

1 Like
#7

hey, 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(:wink: (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.

jm2ct

#8

My 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.

#9

@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.

#10

hi @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.

#11

Concerning 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.

#12

hi @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?

#13

one 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.

#14

to 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.

#15

Well, 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.

#16

(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!

#17

(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.

#18

@Lupp: MANY MANY THANKS, you’ve given me much more than a smile!

60 years of experience … i’ll not! go into the ring :wink:

  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,

  1. 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 :wink: …

#19

as we just talked about automatic conversion …

‘1. and 7. …’ in the above comment was written and meant as ‘6. and 7. …’, but automatically converted :frowning:

#20

This discussion has long gone beyond what fits into this site’s formats. Just one more comment :wink:
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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.