Possible bug in addition of formula result if result is "" (blank)

[dottore]

I have a small spreadsheet that shows this problem and if I can figure out how to attach it or upload it I will.

The problem I’m seeing is this:
I have a formula that generates either a blank or a number. The result of this formula is added to another number. If the formula generates a blank the result of cell+cell is #value, but the result of sum(cell:cell) is correct.

Looks like I can’t upload an example SS so here goes a long and tedious explanation.
Please enter the following and you will see the problem:

Cell A1 1
Cell A3 1
Cell A4 blank (i.e. nothing in this cell)
Cell A5 3
Cell A6 =A3+A4+A5

Cell B3 1
Cell B4 =if(A1=1,"",2)
Cell B5 3
Cell B6 =B3+B4+B5

Cell C3 1
Cell C4 =if(A1=1,"",2)
Cell C5 3
Cell C6 =sum(C3:C5)

If you get the same result I got then you will see the following:
A6 4
B6 #VALUE!
C6 4

This looks like a bug to me. Somehow when the formula evaluates to “”, addition fails, though a simple, non-formulaic “” works fine.

I’m hoping someone will either verify the problem and I’ll report a bug, or explain to me what I’m doing wrong.

[Lupp]

No bug! See answer by @Regina and explanations in the attached demo.

-1- Use SUM(B3;B4;B5) or SUM(B3:B5) instead of B3+B4+B5 to get what you want in conformance with the specifications. SUM() will ignore any text, but this should not spoil anything.

-2- Do not rely on obscure settings. Be explicit! Otherwise you endanger even the rudiments of clarity created by odf specifications.

The attached demo also contains an addendum concerning the conflicts and misunderstandings coming up again and again in this context.

[paravantis]

Good post, thank you!

[JohnSUN]

You can fix it if use in cell B4 formula

=IF(A1=1;;2)

and format result “without zero” (format code 0;0;;@ )

Or use in cell B6 a formula

=B3+N(B4)+B5

I mean that cells with results of formulas additionaly need function N() which will convert empty string to zero

Update In addition, you can simply change the behavior of the program in this part.

[paravantis]

I would like to enhance @JohnSUN’s on-the-spot explanation, by adding that the behavior @dottore describes as buggy is identical to what happens when such a spreadsheet is opened in Excel 2016: the B6 cell gives a #VALUE unless the formulas are corrected as described by @JohnSUN.

It certainly does not look like a bug to me.

BTW, good post, it was a great opportunity to learn something new in spreadsheet functions!

[JohnSUN]

Yes, you’re right. Excel 2003 and Excel 2010 work the same way.

[Regina]

The difference between operator + and the sum() function is, that the operator + expects a single number and the function sum() expects a number sequence. And a conversion to number sequence is handled different in the specification.

Find the specification for formulas in OASIS Open Document Format for Office Applications (OpenDocument) Version 1.2 - Part 2: Recalculated Formula (OpenFormula) Format.

The section 6.3.5 describes, what to do, if the operand does not has the expected type. For text → number the specification says, that it is “implementation-defined”. LibreOffice tries to do the conversion similar to Excel as far as possible. That gives some non obvious results and opening the document with a different application might result in different values. Therefore I’m not happy with the decision to not throw an error in all conversion cases. For example in a German local the text “2.345” converts to the integer number 2345, the text “2.34” results in an error and the text “2,345” results in a decimal number, as the comma is the decimal separator and dot is the thousand-separator in Germany. The conversion is different in an EN-US local. For all this a simple rule is, to never use text with simple arithmetic operators.

The section 6.3.7 describes, how to convert a reference (B3:B5 in your example) to a number sequence. And in this section you find “Thus, Empty cells and Text that could be converted into a value is not included in a number sequence.” So the sum() functions gets only cells, which result in a real number. So here the conversion is unambiguous.

[dottore]

you hit on the exact problem. “conversion to number sequence is handled different…” Why doesn’t + also convert the cell contents to a number? + should go thru the exact same steps that SUM goes thru, just on individual cells vs arrays. That would guarantee that + and SUM always give the exact same results. I think this is a problem in the specification. JMHO, of course.

[dottore]

I appreciate the effort you all have put into this. Seems like a “feature” vs “a bug”, though that is poor consolation to a user.

From a user’s point of view I note the following:

1. this works in OpenOffice calc, at least from 3.0 to 4.3. I.e. #VALUE! is not displayed, you get 4 in all cases. I ran into this problem when I tried to use a SS that had been developed on OO calc.

2. The laws of mathematics define SUM and + as identical mathematical operations. That they should give the same answer in all cases is a mathematical law that should supersede any software specifications. I would argue that the specification is the problem, and that either all cases of the example should fail or none.

3. I don’t care what Excel does. As a user I want 3+""+4 to equal 7, no matter how the “” got put into a cell.

4. Regarding what can be done to convert the equation result to a number type, why does the “” that results from the if statement need conversion when the literal blank does not? Again, I am asking from the user’s point of view, not the programmers. I understand that a null string may be different from an empty cell from a programmer’s point of view, but what the user sees in the cell looks exactly the same.

5. The brilliance of Excel and all subsequent spreadsheets is that it made data manipulation easy for the non-programmer user (and programmers for that matter). It relieved the user of the need to understand the workings of the computer, to understand data types, for example. So make it easy.

Anyway, this isn’t very important in the great scheme of things. Decide whatever you want. I have a good work around. I can enter =SUM(B3:B4) instead of =B3+B4 and I get a sensible result. I’ve been programming since 1964 and I expect bugs and features and am pretty good at figuring ways around them. I think most people who come up against this problem will quickly find a work around (took me 5 minutes, way less time than any of us have spent on this thread).

Again, thanks for the effort everyone put in.

Over and out.

[JohnSUN]

OK, you are completely right. In each paragraph. And I’m grateful to you for this text - he forced me to look again at the corresponding settings of the program. Unfortunately, here I can not add a picture, I will add it to my answer.