We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2019-05-04 17:22:35 +0200

newbie-02 gravatar image

updated 2021-06-01 00:10:47 +0200

Alex Kemp gravatar image


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,



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.

edit retag flag offensive close merge delete


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

Opaque gravatar imageOpaque ( 2019-05-04 18:29:31 +0200 )edit

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.

newbie-02 gravatar imagenewbie-02 ( 2019-05-05 01:03:28 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2019-05-05 08:53:13 +0200 )edit

@Mike Kaganski:

thks for agreeing the problem to be something worth to think about,


you're right, i felt somewhat offended by you in another thread / case (i think it was https://ask.libreoffice.org/en/questi...), 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)


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

newbie-02 gravatar imagenewbie-02 ( 2019-05-05 10:19:30 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2019-05-04 18:53:35 +0200

Lupp gravatar image

updated 2019-05-04 18:55:07 +0200

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.

edit flag offensive delete link more


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

newbie-02 gravatar imagenewbie-02 ( 2019-05-05 09:40:22 +0200 )edit

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 gravatar imageLupp ( 2019-05-05 13:00:53 +0200 )edit

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.

Lupp gravatar imageLupp ( 2019-05-05 13:18:56 +0200 )edit

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



newbie-02 gravatar imagenewbie-02 ( 2019-05-05 18:23:22 +0200 )edit

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

Lupp gravatar imageLupp ( 2019-05-05 19:26:16 +0200 )edit

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

newbie-02 gravatar imagenewbie-02 ( 2019-05-06 00:56:32 +0200 )edit

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

newbie-02 gravatar imagenewbie-02 ( 2019-05-06 06:44:21 +0200 )edit

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.

newbie-02 gravatar imagenewbie-02 ( 2019-05-06 07:28:53 +0200 )edit

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

Lupp gravatar imageLupp ( 2019-05-06 12:35:13 +0200 )edit

"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 gravatar imageLupp ( 2019-05-06 12:57:52 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-05-04 17:22:35 +0200

Seen: 234 times

Last updated: May 04 '19