[Solved] LibreOffice Calc calculation errors

Discuss the spreadsheet application
Post Reply
Stevie
Posts: 15
Joined: Fri Dec 07, 2007 12:31 pm

[Solved] LibreOffice Calc calculation errors

Post by 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.
Attachments
Screenshot Spreadsheet
Screenshot Spreadsheet
Last edited by Stevie on Sun Aug 09, 2020 1:33 pm, edited 2 times in total.
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Libreoffice Calc calculation errors

Post by John_Ha »

Stevie wrote: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.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
Stevie
Posts: 15
Joined: Fri Dec 07, 2007 12:31 pm

Re: Libreoffice Calc calculation errors

Post by 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.
Attachments
Libreoffice Calculation Error.ods
(16.23 KiB) Downloaded 241 times
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Libreoffice Calc calculation errors

Post by John_Ha »

Stevie wrote: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

=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

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.
Clipboard01.png
Adding.ods
(9.2 KiB) Downloaded 247 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.
Last edited by John_Ha on Thu Jul 23, 2020 12:35 pm, edited 1 time in total.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
Stevie
Posts: 15
Joined: Fri Dec 07, 2007 12:31 pm

Re: Libreoffice Calc calculation errors

Post by Stevie »

How do I force Calc to calculate with 2 digits only? I even used the round command.
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Libreoffice Calc calculation errors

Post by 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.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: LibreOffice Calc calculation errors

Post by MrProgrammer »

Stevie wrote: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
Options dialog
Calculation example
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
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
Stevie
Posts: 15
Joined: Fri Dec 07, 2007 12:31 pm

Re: [Solved] LibreOffice Calc calculation errors

Post by 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.
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: [Solved] LibreOffice Calc calculation errors

Post by 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
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] LibreOffice Calc calculation errors

Post by 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...
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: [Solved] LibreOffice Calc calculation errors

Post by John_Ha »

Stevie wrote: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 AOO
Calculated in LO
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.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
Posts: 9583
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: LibreOffice Calc calculation errors

Post by John_Ha »

MrProgrammer wrote:
 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.
Clipboard01.png
Circular references used incorrectly.ods
(10.78 KiB) Downloaded 233 times
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
Stevie
Posts: 15
Joined: Fri Dec 07, 2007 12:31 pm

Re: LibreOffice Calc calculation errors

Post by 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.
Post Reply