(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

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ž

Perhaps related to or same as Bug 37923, also contain subtraction

(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.

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 ***

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).

*** Bug 86005 has been marked as a duplicate of this bug. ***

*** Bug 37923 has been marked as a duplicate of this bug. ***

*** Bug 80017 has been marked as a duplicate of this bug. ***

*** Bug 90997 has been marked as a duplicate of this bug. ***

*** Bug 92536 has been marked as a duplicate of this bug. ***

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.

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....

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.

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.

@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 ...

The title is:

> Limitations in Calc precision need to be documented

https://wiki.documentfoundation.org/Faq/Calc/Accuracy

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