Bug 67026 - [LOCALHELP] Limitations in Calc precision need to be documented
Summary: [LOCALHELP] Limitations in Calc precision need to be documented
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
Version:
(earliest affected)
3.5 all versions
Hardware: Other All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA target:7.4.0
Keywords:
: 80017 86005 90997 92536 (view as bug list)
Depends on:
Blocks: Help-Changes-Features
  Show dependency treegraph
 
Reported: 2013-07-18 08:22 UTC by Iker De Echaniz
Modified: 2024-08-04 07:37 UTC (History)
11 users (show)

See Also:
Crash report or crash signature:


Attachments
Same forumula, different results - part of rounding issues (8.90 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-09-02 17:04 UTC, Robin
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Iker De Echaniz 2013-07-18 08:22:20 UTC
Problem description: 

If you multiply  0,000005 by 1000000 the result is 5

Playing with GPS coordinates, at one cell I did 43,271815-43,27181 and it gives 0,000005 exact but if you take that result and multiply it by 1000000 the result is 4,9999999945

LibreOffice 3.5.7.2
Build ID: 350m1(Build:2)
Ubuntu 12.04 x86_64

Current behavior:
4,9999999945

Expected behavior:
5

Operating System: Ubuntu
Version: 3.5 all versions
Comment 1 Tomaz Vajngerl 2013-07-18 10:12:42 UTC
(In reply to comment #0)
> Problem description: 
> 
> If you multiply  0,000005 by 1000000 the result is 5
> 
> Playing with GPS coordinates, at one cell I did 43,271815-43,27181 and it
> gives 0,000005 exact but if you take that result and multiply it by 1000000
> the result is 4,9999999945
> 
> LibreOffice 3.5.7.2
> Build ID: 350m1(Build:2)
> Ubuntu 12.04 x86_64
> 
> Current behavior:
> 4,9999999945
> 
> Expected behavior:
> 5
> 
> Operating System: Ubuntu
> Version: 3.5 all versions

Hi,

Unfortunately such errors are caused by the real number representation (called floating-point) in computers and happen because of limited capacity to store the numbers and because you can not represent some decimal fractions (like 1/10 -> 0.1 for example) exactly in binary. Read more about this in [1]. I think that Calc sometimes works around the issue by using fraction representation (represents numbers in numerator / denominator form) if it is possible but sometimes (like in your case) it can not. Usually in such cases you need to round the number to a certain decimal places you think it makes sense with ROUND operator. For example (=ROUND((43.271815-43.27181) * 1000000, 5)) if you want to round to 5 decimal places.

Regards, Tomaž

[1]: http://docs.python.org/2/tutorial/floatingpoint.html chapter Floating Point Arithmetic: Issues and Limitations
Comment 2 Tomaz Vajngerl 2013-07-18 10:22:29 UTC
Hi,

I changed my mind and reopened this bug. The reason is that I think this is something very important that should be put into the documentation. Can someone check the documentation if this is included and add if it is not, add it. I will change the component from Calc to Documentation.

Regards, Tomaž
Comment 3 ign_christian 2013-07-18 12:41:17 UTC
Perhaps related to or same as Bug 37923, also contain subtraction
Comment 4 Owen Genat (retired) 2014-02-07 23:42:53 UTC
(In reply to comment #3)
> Perhaps related to or same as Bug 37923

Almost certainly a duplicate. Summary edited for clarity.

(In reply to comment #2)
> I changed my mind and reopened this bug. The reason is that I think this is
> something very important that should be put into the documentation. Can
> someone check the documentation if this is included and add if it is not,
> add it. I will change the component from Calc to Documentation.

Agreed. Can someone add the Documentation mailing list to the CC list for greater visibility and to solicit comment? I tried and failed. I had a look through the Calc Guide v4.1 and note:

- "precision" references are mainly to the "Precision as shown" option (pp.222, 361-362) and the EUROCONVERT() function (p.388). I note the entry for "precision" in the index indicates p.354, which seems unrelated. Also, note that the "Precision as shown" option appears dependant on the cell format not being General (it must be a Number) as recently discussed here (in the comment by Mark Bourne):

http://nabble.documentfoundation.org/Error-or-what-td4095174.html

- " rounding" (leading space to avoid some false positives) references are mainly to the topic of "Rounding off numbers" (pp.221-222) and the "Decimal places" display option when formatting cells (pp.362), as well as the FLOOR() and CEILING() functions.

It would be good if this limitation, in how integer values are handled (generally by all computers) affects precision in calculation, could be added to the beginning of the Calc Guide somewhere (the Preface?). IMO this needs to be clearly highlighted using the "Caution!" block style (shown on p.174) and preferably present a clear example of basic addition and subtraction using large and small values. This would enable the developers, QA, and others to point out a location where this limitation is officially described.

I can't find any information related to this topic (using key terms such as "Calc" + "precision", "rounding", or "limitation") in either the LO Help website or TDF wiki. It should be added to these repositories also.
Comment 5 Joel Madero 2014-11-06 00:44:05 UTC
Marking as a duplicate as two have suggested that - if it's not a dupe please set to NEW not REOPENED. Thanks

*** This bug has been marked as a duplicate of bug 37923 ***
Comment 6 Robinson Tryon (qubit) 2014-11-11 19:45:38 UTC
I don't think this is a dupe.

This bug is a request to describe the current behavior and limitations in Calc (Documentation).
Bug 37923 is an enhancement request to improve/increase the current limits in the code (Development).
Comment 7 Robinson Tryon (qubit) 2014-11-11 21:12:12 UTC
*** Bug 86005 has been marked as a duplicate of this bug. ***
Comment 8 Robinson Tryon (qubit) 2014-11-11 22:31:50 UTC
*** Bug 37923 has been marked as a duplicate of this bug. ***
Comment 9 Matthew Francis 2015-04-15 16:51:00 UTC
*** Bug 80017 has been marked as a duplicate of this bug. ***
Comment 10 Matthew Francis 2015-04-23 03:24:36 UTC Comment hidden (obsolete)
Comment 11 m_a_riosv 2015-05-02 00:03:32 UTC
*** Bug 90997 has been marked as a duplicate of this bug. ***
Comment 12 m_a_riosv 2015-07-04 09:10:53 UTC
*** Bug 92536 has been marked as a duplicate of this bug. ***
Comment 13 Andrew 2015-07-04 11:45:12 UTC
I'm going back on my high horse so please bear with me, there is a reason.

The fact that a multiplication or a division can lead to errors in rounding is a well known problem and requires the use of such as functions as ROUND().

My problem is that, under certain circumstances, addition and subtraction bring in rounding errors. I understand that numbers are saved in floating point and that Excel has the same problem but if cell A1 is loaded with a number with limited decimals (say 15,02) and cell B1 has a value of 10,02 I should expect to always get A1+B1=25.04 and not 25.03999999999. My main beef is that both numbers are inputed and not calculated (so I KNOW they have ONLY two decimal places) and thus output should be ALWAYS on two decimal places.

There is something that would need to be done to how inputed numbers are memorized, I realize that it will never happen because it would be a major piece of work (and I no longer have the necessary programming skills). But, please, no answers such as the one I received "Excel does it too": if somebody else does something wrong that doesn't justify me repeating the same mistake!

However I would really like to see a more precise piece of documentation which explains exactly under which conditions does an addition or subtraction of numbers inputed with a fixed number of decimals give a result that is not equally exact.
Comment 14 Joel Madero 2015-07-04 17:30:20 UTC
Well - it's going to be guessing and checking. Feel free to join the documentation team and contribute there. Wiki is open access - you can create a page and start documenting where you see problems (going off of the sample files and duplicates on this bug). Pretty easy thing to do so telling others to do it makes no sense if it's so important to you....
Comment 15 QA Administrators 2016-09-20 10:14:04 UTC Comment hidden (obsolete)
Comment 16 Robin 2017-09-02 17:01:19 UTC
Today my spouse pointed out a simple error in calculation in a spreadsheet.  Simple addition and subtraction used for money but cells formatted as numbers and general.

Version: 5.3.5.2
Build ID: 5.3.5.2-3.fc26
CPU Threads: 8; OS Version: Linux 4.12; UI Render: default; VCL: gtk3; Layout Engine: new; 
Locale: en-CA (en_CA.UTF-8); Calc: group

What is interesting is I ended up with two different results within a spreadsheet for the same numbers though the difference is a rounding error.

I understand the reason this is happening but I don't understand why in a spreadsheet, I get two different rounding results for the same number.
Comment 17 Robin 2017-09-02 17:04:07 UTC
Created attachment 135971 [details]
Same forumula, different results - part of rounding issues

This is a test spreadsheet to test this bug and rounding for straight addition.  One cell is correct with the others showing the rounding issue.  Raises an interesting question of how it occurred.
Comment 18 b. 2020-10-04 21:25:14 UTC
@Andrew: 

'if cell A1 is loaded with a number with limited decimals (say 15,02) and cell B1 has a value of 10,02 I should expect to always get A1+B1=25.04 and not 25.03999999999. My main beef is that both numbers are inputed and not calculated (so I KNOW they have ONLY two decimal places) and thus output should be ALWAYS on two decimal places.'

for theese values it works in recent LO versions, but there are some numbers which produce irritating results, 

i had the same idea and tried if it is possible to implement 'smart rounding' ... it is ... it is slow, as done with the tools available to me (user macros), but ... it works, 

perhaps you'd like to test, and perhaps sometimes somebody will implement something similar or better in code, 

it doesn't help for very precise numbers with lots of digits, but i'd bet that about 70 percent of numerical data hold and calculated in spreadsheets are financial data with 2 or max three decimals, 

and that more than 95 percent of the complaints about wrong calculations in spreadsheets result from users who didn't expect wx,yz999999~ values for their money, 

you find a version of my tests in http://bugs.documentfoundation.org/attachment.cgi?id=165435, or in http://bugs.documentfoundation.org/attachment.cgi?id=165424, 'SUM_S' doe's a calculation and smart rounding of two numbers ... 

@Robin: 

which are your 'different results'? couldn't find any in the sample with LO 6.1.6.3 or 7.1.0.0.a0+, or do you mean I10 and J10? they have different display format, 14 and 16 decimal digits, that's not an issue of calculation but of user formatting ... 

doesn't say that there are no errors respectively very questionable decisions how which numbers are processed and displayed (int 16 digits, floats 15, crazy as producing crazy number sequences), just this sample is OT ... or did i miss something? 

'Raises an interesting question of how it occurred.' - spreadsheets calculate with binary 'float' numbers while humans are used to decimals, for many decimal numbers there is no exact correspondence in the binary system, especially not with a limited number of bits, so the calculation is done with the closest value that can be constructed in IEEE 754 float numbers, and also the result is not! the - often rounded - number that is displayed on the screen, but a binary value with which calc continues to calculate in the background, thus errors can add up, 

mostly save: multiplication and division, critical: adding numbers with different magnitude and subtracting numbers with small difference compared to their value, special: special functions like like modulus or angle functions ...
Comment 19 Mike Kaganski 2020-10-20 12:22:14 UTC
The title is:

> Limitations in Calc precision need to be documented

https://wiki.documentfoundation.org/Faq/Calc/Accuracy
Comment 20 Commit Notification 2022-01-31 11:53:57 UTC
Olivier Hallot committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/help/commit/69fb882589e0accf46cdaef9cc5e01cc47358137

tdf#67026 Limitation of Calc precision documented