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

[newbie-02]

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

[Lupp]

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.

[newbie-02]

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

[Lupp]

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.

[Lupp]

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

[newbie-02]

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.

[Lupp]

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.

[newbie-02]

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?

[newbie-02]

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.

[newbie-02]

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.

[Lupp]

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.

[Lupp]

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

[Lupp]

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

[newbie-02]

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

[newbie-02]

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

[Lupp]

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

[newbie-02]

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

[Lupp]

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

[Lupp]

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

[Lupp]

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