spreadsheet not calculating correctly

users
#1

An oddity that's popped up, but I can't repeat the effect in another spreadsheet.

My spreadsheet (freshly loaded) has 3 particular cells:
B5 completely empty
C5 33520
D5 =SUM(B5:C5)

D5 currently wrongly displays 0, and F9/recalculate doesn't change this.

Now, if I set B5 to 0, D5 changes to 33520 (at last correct!), and if I ^Z to undo the change, D5 remains showing 33520. Likewise, changing C5 makes D5 update correctly, or indeed if I retype the SUM() expression. (The only way of getting the wrong result back is to reload the spreadsheet from disk)

So, I have a spreadsheet showing a wrong calculation result. Thankfully, 0 was a pretty obvious error.

Any thoughts?

(This is LO 4.4.4.3 on Mint 17)

The relevant row from the contents.xml file contains:

<table:table-row table:style-name="ro1"><table:table-cell table:number-columns-repeated="2"/><table:table-cell table:style-name="ce2" office:value-type="float" office:value="33520" calcext:value-type="float"><text:p>33520</text:p></table:table-cell><table:table-cell table:style-name="ce2" table:formula="of:=SUM([.B5:.C5])" office:value-type="float" office:value="0" calcext:value-type="float"><text:p>0</text:p></table:table-cell><table:table-cell table:number-columns-repeated="1020"/></table:table-row>

#2

Do you have a sample file with this error?

Using an older version (4.4.1.2) I can't seem to reproduce this behavior.

#3

This is looking like an issue that will not be found easily. Simply
opening your file, the result is correct: D5 initially show 33520.

Out of curiosity, I tried opening it in gnumeric, same result. Only
google drive *preview* show the 0 in D5. Interestingly enough, when
opening the sheet fully in google drive, D5 shows up as "£33520" for no
apparent reason.

At this point, if you found a way to reproduce it, it might be worth
opening a bug, but only after checking that your LO profile is not at
fault. You could try renaming it to check that.

Thanks for checking.

I've just done what I should have done in the first place - try a different machine. That one had LO 4.2.8.2 - and works fine. So maybe the issue is specific to the later version of LO (4.4.4.3).

Removing my LO profile had no effect.

The spreadsheet was originally a financial one, but I removed all the 'currency' formatting when I stripped out all the irrelevant cells. Odd that google seems to put it back.

I have to say, I don't like unrepeatable errors of unknown cause. In this case the fault was obvious; but what if it hadn't been clear?

As it is unrepeatable, I guess I'll archive the problem file and let the issue drop; and pay extra careful attention to results in future.
>

Thanks again.

#4

More on my erroneous spreadsheet. I see what's going on, if not why.

I've removed rows and columns, so my spreadsheet is now
A1: blank
B1: 33520
C1 =sum(a1:b1)

Looking in the content.xml file, cell C1 contains the following:

<table:table-cell table:style-name="ce2" table:formula="of:=SUM([.A1:.B1])" office:value-type="float" office:value="0" calcext:value-type="float"><text:p>0</text:p></table:table-cell>

The office:value="0" is what is displayed when the spreadsheet is loaded, while <text:p>0 seems to be ignored. (To show this, I extracted content.xml, hand-edited these to be 999 and 888, and put the file back in the archive, then reopened with Calc. C1 displayed 999)

I have no idea why there should be any values stored in this way - should a spreadsheet not populate itself by calculating when loaded? Presumably the behaviours of 4.4.4.3 and older versions differ in this regard, 4.4.4.3 presumably taking on trust the stored values.

Could someone running a later version check this out please?

It still leaves the question of how the erroneous '0' came to be stored in the first place: it looks as though the stored values are supposed to be the value calculated (3352), storage of which for some reason went wrong somewhere.