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.