[Solved] LibreOffice Calc calculation errors

[Stevie]

Hello,

Libreoffice Calc is doing calculation errors.

Spreadsheet 1 AV8 is empty. This calculation error disappears If I change the formula in AV7 to a number. The result also stays correct if I do an "undo" after that so that the formula appears again.

The result is also corrected when I delete the field AV9 with "=spreadsheet1.av8".

I have the whole row with that calculations. In some fields in the beginning of that row the calculation is correct but as more I move to the right the errors appear. It's not always the same miscalculation. Sometimes it's only 0,01.

Neither Openoffice or Excel do those miscalculations.

[John_Ha]

Libreoffice Calc is doing calculation errors.

I very much doubt that.

Your description of the problem does not explain it well enough to offer advice.

Please upload a small .ods file showing the problem so that it can be analysed.

Press POSTREPLY and click the Upload attachment tab below where you type (128 kB max); or use a file share site, Dropbox or Google Drive for a larger file.

[Stevie]

Okay, I shortend the document and the error should still be visible. As I understand those spreadsheet programs there is a different between what they show in the cells and with what they are calculating. I think there is an error that keeps adding up the longer the calculation is.

The error should be visible near the end to column BZ.

[John_Ha]

I think there is an error that keeps adding up the longer the calculation is.

It is not a Calc error - it is a user error because the user does not understand how Calc (or any spreadsheet) operates.

If I set A1, A2and A3 to

Code: Select all   Expand viewCollapse view

=1/3

then each cell contains 0.333333333333333. That is an accuracy equivalent to measuring the distance from London to New York to about 1/100 of 1 millionth of an inch.

If I choose to set the cells so that only 2 digits show I see 0.33 but the cell contains 0.333333333333333. Calc calculates with the actual cell values, not the shortened versions the user chooses to display.

When I sum A1 + A2 + A3 I see 1.00, and not 0.99, because 0.333333333333333 + 0.333333333333333 + 0.333333333333333 is equal to 1. There is a minor factor that Calc carries a non displaying extra digit which forces the sum to be 1.00000000000000.

Code: Select all   Expand viewCollapse view

0.33
0.33
0.33
1.00

If I choose to display no figures after the decimal I see 0 + 0 + 0 = 1, which is, of course, correct as Calc has used the actual value for the sum.

Adding.ods

(9.2 KiB) Downloaded 73 times

So, in your spreadsheet set each cell to display, say, 6 digits. You will see that C1 is actually 5.10100..., and not the displayed 5.1, a "difference" of 0.001. Sum lots and the "differences" accumulate to be as large as you want.

[Stevie]

How do I force Calc to calculate with 2 digits only? I even used the round command.

[John_Ha]

It depends on what you are doing and what accuracy you need.

Decimal values (apart from powers of 2 such as 0.5, 0.25 etc) cannot be represented exactly in binary form.

A foolproof way is to use integer values, which is exact (until you divide!). So, if you are using dollars and cents, enter values as cents and calculate in cents. But display in dollars, calculated as cents/100. 472 cents will display as $4.72 and calculated and display values are equal.

Or round every calculation result but beware if you do as, in principle, it is best to calculate with accurate figures throughout and round only the final answer otherwise the errors continually accumulate.

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.

[MrProgrammer]

How do I force Calc to calculate with 2 digits only?

See if the option Calc → Calculate → Precision as shown does what you want. This option tells Calc to use the numbers shown on the screen, not the values stored in the cells, when you reference cell contents in a formula. I believe this is what you asked for. Options are set with OpenOffice → Preferences on a Mac, Tools → Options on other platforms. This option makes calculation results depend on formatting, which I would not like for my spreadsheets, but perhaps it helps with your situation. One third plus one third plus one third can now be 0.99 or 0.9 or zero! Note that the value in the red-circled cell is really zero, as shown by using it as a divisor. Precision as shown is a global option and affects every calculation in your spreadsheet, thus you must design your spreadsheet with this option in mind. My text in column A uses Ω as a placeholder for the column name.

Options dialog

Calculation example

Edit: 2020-08-06: I just noticed that the Libreoffice Calculation Error.ods has enabled option Calc → Calculate → Iterations. I know of no documentation that provides the details about how formulas are calculated which use that option, thus, far as I am concerned, all those calculations are undefined, whether using Precision as Shown or not. There are numerous topics on the forum about strange results if Iterations is enabled. I cannot help anyone who is using that option for their spreadsheet.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Stevie]

Mr. Programmer, I tried that 'precision as shown' already. It changes the results but they're still wrong.

John, I stand by my assessment. Those ARE calculation errors. You can doubt is as much as you want. Be a fool.

I don't do any complicated scientific calculations. Those are very simple calculations. I used the round command which isn't just a cosmetic changes the output display of a calculation. The round command has to change the result itself. So any further calculations has to use this rounded number as base for the next calculation. So the results can't differ by more than 0.01. I already said that Openoffice and Excel handle it right. Libreoffice is the unreliable tool only.

So the problem stays unsolved until they fixed that bug.
I only wrote here, cause I hoped the Libreoffice support would be here, too. But seems they are in the Libreoffice forum only. Doesn't make sense to continue here.

[robleyd]

As with this forum, you'll need to file a bug report if you want to alert LO developers to a problem. https://wiki.documentfoundation.org/Ask ... arted#Bugs

[Zizi64]

I only wrote here, cause I hoped the Libreoffice support would be here, too

Any user forum is not an official Support. There is not any "official Support" for the free open source office suites.

You can report the bugs in the bugreport sites. And you must wait after...

[John_Ha]

Neither Openoffice or Excel do those miscalculations
...
John, I stand by my assessment. Those ARE calculation errors. You can doubt is as much as you want. Be a fool.

When I setup AOO and LO to be the same I get identical results to 15 decimal places.

They are not "LO calculation errors" - they are user errors caused by a user who does not understand what he is doing.

Calculated in AOO

Calculated in LO

Do you understand that you are using circular references? Do you understand the implications of doing so?

As such, the accuracy of your answers will be dependent on the number of iterations and the minimum change. With few iterations, where the result does not converge, AOO will probably produce a slightly different result from LO as AOO is 32 bit and LO is 64 bit and you are exploring the fine differences in number representation. Set the iterations high enough for the result to converge and both AOO and LO give identical results.

It would be interesting to repeat the test with 64 bit AOO on MacOS where I would expect the results to be identical at much lower iteration counts.

[John_Ha]

Edit: 2020-08-06: I just noticed that the Libreoffice Calculation Error.ods has enabled option Calc → Calculate → Iterations. I know of no documentation that provides the details about how formulas are calculated which use that option, thus, far as I am concerned, all those calculations are undefined, whether using Precision as Shown or not. There are numerous topics on the forum about strange results if Iterations is enabled. I cannot help anyone who is using that option for their spreadsheet.

I think the best one can say is that one should only ever use circular references if one knows the calculation will converge as, for example, in a converging Newton-Rapheson calculation of the root of an equation. If so, the number of iterations should be increased until the calculation converges to a stable value of the required accuracy.

If the calculation does not converge to a stable value the result is completely indeterminate as can be seen in the spreadsheet where the first calculation increases with every calculation and is completely indeterminate. The second calculation continues to diverge but very slowly.

See All You Need to Know for Using Excel Iterative Calculation. Excel defaults to 100 iterations.

The only mention of circular references in the LO manual is to Error codes 522 and 523.

Circular references used incorrectly.ods

(10.78 KiB) Downloaded 68 times

[Stevie]

John, now we come to the point. You could have kept your first messages. The calculation error is not were I thought. Circular References is the important thing. I forgot that I changed my spreadsheet long ago to have the whole calculation in one column which is one month. I understand that iterations are hard to handle. But the Option "precision as shown" has a bug. It's not calculating with the results that are visible in the spreadsheet. Well, I disabled iterations and calculate without it now.