Created attachment 140238 [details] screenshot of the bug, libreoffice info + bugged .ods Very strange bug: sum() seems to not to work in a trivial usage. All info in attached files. cells in red show bad sum() value while green cell shows right value. yellow cells has formula inside, while orange cells dont. However only the white column show the correct sum() value (green) at the end. See attached png what i see and libreoffice details. The correct sum is 3793.58 (nor -46664.52 or 11493.58)
Thank you for reporting the bug. it seems you're using an old version of LibreOffice. Could you please try to reproduce it with the latest version of LibreOffice from https://www.libreoffice.org/download/libreoffice-fresh/ ? I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' if the bug is still present in the latest version.
Created attachment 140239 [details] screenshot of the bug, libreoffice info + bugged .ods (second upload) this overwrite "screenshot of the bug, libreoffice info + bugged .ods"
Created attachment 140240 [details] screenshot of the bug, libreoffice info in ver 6.0.1.1 Downloaded ver 6.0.1.1. confirmed as showed in attached, new screenshot. Se columns "Not OK".
Attachment #140238 [details] shall be deleted.
When you set the cell alignment to default, you will see that some cells in column B are aligning left, which means that the values in these cells are actually text, not numbers. If you change the formula contents of the cells, for example, change cell B4 from "=47471*1.1" to "=47471*1.2" and hit enter, it becomes numbers and automatically align right. So, the point is, how do you generated this document, and how these cells become text values, rather than number values? There was a similar bug report recently and was marked as WORKSFORME as it was found that the cell values are text with a "'" before numbers. Will try to fint that one.
Well, the similar bug I mentioned is Bug 115642.
Created attachment 140250 [details] doc exported from google doc
(In reply to Kevin Suo from comment #5) > When you set the cell alignment to default, you will see that some cells in > column B are aligning left, which means that the values in these cells are > actually text, not numbers. > > If you change the formula contents of the cells, for example, change cell B4 > from "=47471*1.1" to "=47471*1.2" and hit enter, it becomes numbers and > automatically align right. Ok, right. I confirm is as you sayd. But I think you also agree that the user have no evidence of this difference. It seems bad to change 1.1 to 1.2 and then change it again to 1.1 to have the right value. Something still dont work I think. How is clear that one is text and others are numbers? It is not. Moreover how they can be "text" if are a result of forumula corretly computed (=47471*1.1 = 52218.1 ) > > So, the point is, how do you generated this document, and how these cells > become text values, rather than number values? From google doc. I loaded attachment 140250 [details] now the original doc that generates it from google doc "File->Download as->Opendocument". I can also share with you if you want the doc Google, but privately, write me to fabrizio at bzimage dot it you need google account. > > There was a similar bug report recently and was marked as WORKSFORME as it > was found that the cell values are text with a "'" before numbers. Will try > to fint that one. yes Bug 115642 seems to me to be the same.
Note that the attachment 140250 [details] shows the right sum(). However the formula of the sum have strange value (also in the original google doc is so): =sum(D3:25) and not =sum(D3:D25) as expected. This was a typo of the original doc in google. However, when this is exported to opendoc it becomes: =sum(D3:Z25) and Z is introduced. That still show right value. But when i manually changed to expected formula: =sum(D3:D25) it shows the bad value, and have no possibility to understand what's wrong: this is the bug i think. Try to do it.
(In reply to Fabrizio from comment #9) Yes, I guess there is sth going wrong here, but I also guess it's google DOC who exported an invalid ODF document in which LibreOffice is treating some of the cells as text. @xisco fauli: If you have time, would you contact the reporter to see the document on google docs to check what's going on? Google docs is doing the correct calculation, but libreoffice is behaving differently. I am not able to access google docs because it's blocked in China.
Set platform to ALL as I see the same behaviour in Win 10.0.
it's generated with value-type="string. Example bad: <table:table-cell table:style-name="ce4" table:formula="of:=800*1.1" office:value-type="string" office:string-value="880" calcext:value-type="string"><text:p>880</text:p> good: <table:table-cell table:style-name="ce5" office:value-type="float" office:value="350" calcext:value-type="float"><text:p>350</text:p> compare with text cell: <table:table-cell table:style-name="ce1" office:value-type="string" calcext:value-type="string"><text:p>TOTAL</text:p>
Well, I think we can set it to new. It's up to the devs to decide.
When investigating the the document internally, like raal did in comment#12, it becomes apparent that the document itself is at fault, not Calc. This is a document created by Google Docs (see comment#7), not by Calc. Proper ODF documents contain a reference to the application which created/saved the document, this document does not contain any such reference, so I can't see which application/version exactly was used to save the document. The formulas in the documents are saved incorrectly, with the result that they appear as formulas, but do not work as such. It can be demonstrated by recalculating the formulas: select any cell with a formula (=something) and press F9. Now you see the correct result of the formula. For cells D4, D7, D9, D10, D11 and D23 the result seems to be a realignment from left to right, but in reality it is a change from text (incorrect) to numeric value (correct). For cell D26, the change is twofold: a change from text to numeric value and a recalculation with a different value as result (depending on whether D4, D7, D9, D10, D11 or D23 have been recalculated). I cannot explain why the formulas in D5 and D6 are saved correctly in the document. As my conclusion is that Google Docs created a faulty document, it a Google bug and not a Calc bug. Setting the status as such.