Confirmed on LO 3.6.3rc1 Windows Vista 32 bit.

It looks like it is using Floating Point arithmetic instead of decimal to calculate the values.

Still reproducible (both in example and for new sheets) under Ubuntu 10.04 x86_64 running v4.1.3.2 Build ID: 70feb7d99726f064edab4605a8ab840c50ec57a.

Still repro.

Win 7 Pro 64-bit Version: 5.1.0.0.alpha1+
Build ID: 437210d58f32177ef1829d704f7f4d2f1bbfbfdd
TinderBox: Win-x86@39, Branch:master, Time: 2015-06-18_07:21:56
Locale: fi-FI (fi_FI)

Created attachment 122375 [details]
Floating point error when dragging a list to auto fill

Demonstrates deterioration of auto filled incremental lists. Possible floating point error.

Confirmed in 5.0.4.2

Attachment added.

Possible floating point error.

Can not reproduce the error with, tested with both examples.

Version: 6.0.0.0.alpha0+
Build ID: 41b7713334351d7cc455eef5241bd3988b9d1e94
CPU threads: 8; OS: Linux 4.9; UI render: default; VCL: kde4; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2017-09-13_22:56:21
Locale: nl-BE (en_US.UTF-8); Calc: group

Tested with LO 6 on Linux and Windows and the bug is fixed. Closing.

dev version:
http://dev-builds.libreoffice.org/daily/master/

reopening, 

this bug is still - or again - present in ver. 

Version: 6.4.0.3 (x64)
Build ID: b0a288ab3d2d4774cb44b62f04d5d28733ac6df8
CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: 

sequence 1:  -2, -1,95 ... produces the values as in sample https://bugs.documentfoundation.org/attachment.cgi?id=68541 when re-producing it in a new column, 

while the second sequence: -1, -,9 ... which showed errors around 0 an 0,1 for me, and the third sample from https://bugs.documentfoundation.org/attachment.cgi?id=122375 look 'healed', when re-producing they produce 'exact decimals' (at least for some 300+ rows i tested and if i didn't overlook something), 

@Mike has done some work on this issue in https://bugs.documentfoundation.org/show_bug.cgi?id=129606, 

the still reproducible error may have re-occured for any reason, or might have been overlooked by @Xavier and @raal in c#7 and c#8 because it doesn't show up if the column is too narrow, then the numbers are shortened / folded / rounded to two decimal places and the error looks gone, it's easier spotted in the input-line / formula-bar, or when you broaden the column, and format the cells to show 20 decimal places. 

the bug - better 'the problem' - is widespread - see #90419, #128312, #129606, #130221, #130356 and plenty others - it isn't really a bug as @Mike explained in some of the mentioned threads, it's just 'unavoidable' as the representation of decimal fractions in binary 'floating point format' isn't exact for most values but includes - very small - rounding errors. 

once you know this you can avoid such problems by using clever rounding or formatting, or you can learn to live with it as you know the source of the problem. 

but it will take plenty people much time for research when they hit this problem for the first time and had expected a spreadsheet to do exact calculations. they won't start a research for 'limitations when representing decimal values in binary IEE 754 floating point format', but will put questions like 'my spreadsheet produces wrong results' in 'ask', 'bugs', or in other forums. 

thus i suggest to keep the 'bug' as an enhancement request and check whether it's possible to avoid it in general (or for more use cases) by applying some rounding (make it optional with a switch 'use decimal friendly rounding' to avoid conflicts with scientists who like to keep their 'exact' results, and / or implement IEEE 754-2008 'decimal' format for correct decimal calculations in general. 

-- 'exact' scientific calculations made with calc (or other spreadsheets?) are not really exact in most cases, they have very good - but limited - precision, as the representation in 'binary floats' affects most input values, and even if the input is exact the result of calculations will be affected by rounding issues in plenty cases?? it's only small errors, but if you try 'chaos calculations' or wheather forecasting where 'the flap of a butterfly's wings at the Amazon can trigger a typhoon in the Philippines' ... you need to know about the limitations of the tools you use ... the error is just easily spotted by humans in cases as the attached cases, but it's affecting other values too, and there it's 'invisible' -- 

reg. 

b.

Correcting status to NEW

there have also been improvements made by @Eike Rathke in bug #90419, could something from that help for this bug?

The code introduced to fix bug 90419

    commit e89c0e4fb783bd36d5f5fea154ee8608e542dae4
    CommitDate: Mon Jun 13 13:53:19 2016 +0200

        Resolves: tdf#90419 diminish precision error in Series Fill

was augmented to fix bug 129606 with

    commit 8f46501233c164ff91d77a7f5adf74ea16cd0165
    CommitDate: Wed Jan 8 15:57:19 2020 +0100

        tdf#129606: limit precision in ScTable::FillAnalyse

and in fact in current master this bug here is not exactly reproducible anymore, but the imprecision instead starts at -0.1 with -0.0999999999999999


@Mike: would it make sense to limit precision even one digit further, or would we enter hell from another entrance then?

(In reply to Eike Rathke from comment #12)
> and in fact in current master this bug here is not exactly reproducible
> anymore, but the imprecision instead starts at -0.1 with -0.0999999999999999

Sorry, I couldn't see a problem with sequences of -2,-1.95,... and -1,-0.9,... in the first 100 cells - do I misunderstand what should I check?

In the Input Line.

(In reply to Eike Rathke from comment #14)
> In the Input Line.

Ah yes, sorry. Blind me; must had missed that because of being tired in the evening :-)

I don't think we need to limit the precision of calculation of the increment here. The call in approxDiff is

> return rtl::math::round(0.050000000000000044, 15);

and it returns 0.050000000000000003, which is the closest binary representation to 0.05 (see https://www.binaryconvert.com/result_double.html?decimal=048046048053 ) - i.e., no additional limiting would improve this.

In double precision,

> 0.05*39=1.9500000000000002
> -2+0.05*39=-0.049999999999999822

First idea is that if we returned not only the increment from the approxDiff, but also the number of decimals for rounding (which is 15 in this case), and then used the rounding in the following calculation of the resulting increment for each cell, then this specific sequence would be OK.

Yet, this is not a full solution, since this would fail with longer sequences, like "-22;-21.95;...". That is because when multiplying the atomic increment, we also need to decrease resulting absolute precision proportionally (so if initially it's 5E-16 for 0.05, then for 0.05*39, it's 5*E-16*39=1.95E-14, so we need to round the result of 0.05*39 to 14 decimals).

Eike: if agreed, I suggest to make this an easy hack.

Ah well, we can try, but if no one shows up to pick it we should do.

Code pointers:
sc/source/core/data/table4.cxx approxDiff() and its use in ScTable::FillAnalyse() and all places in that file in functions ScTable::*Fill*() that use SubTotal::SafeMult() and SubTotal::SafePlus().

just an idea ... 

intelligent rounding may lead to intelligent faults? 

the user expects a value / number fitting his input and his idea what to calculate, 

A) would it work to round to the number of digits the user formatted the cells to ... ??? 

B) or just bring 'precision as shown' to work, 

A: the format of the input cells is applied to the targets on dragfill? either from them being formatted as 'two decimal places' or from having input of that length it would be legitim to round the dragfill results to that frame? (i know that @Mike doesn't like the format affecting the result ...) 

B: i tried dragfill with 'tools - options - libreoffice calc - calculate - precision as shown' activated, and expected the artefacts to go away ... they didn't ... :-(
even calculating a series of "=x+,05" starting from -2 with cells formatted to two decimals and precision as shown activated produces 'artefacted values', you can see that by replacing the formulas with their results (strg-c - shift-strg-v with 'formulas' deactivated) while still formatted to two decimals, and then observing the input line or reformat the cells for more decimal places ... 

maybe i misunderstood the idea, concept or power of 'precision as shown'??? but it was once recommended to me as a solution against fp rounding errors

tested with 6.2.8.2 winx64 unpar. 

reg. 

b.

one more suggestion ... inspired from: 

> 0.05*39=1.9500000000000002
> -2+0.05*39=-0.049999999999999822

the first calculation is 'two decimals' times 'integer' ... 

users would expect, and mathematical correct would be! - a result with max two decimal digits, thus just round to that, 

same applies to second sample, 

and e.g. 3,45 * 4,567 'two decimals' times 'three decimals' could / should frankly be rounded to max six decimals, 

addition / subtraction: round to max decimals of summands, 

multiplication: round to product of count of decimals, 

division: no chance, 

exponentiation: simple exponents: round to exponent times decimals in mantissa, fractional exponents: no chance, 

radication: no chance, 

other calculations ... are patched together from above? 

handling like that won't be 'easy' - as the float's dont carry the info about the source value they came from, but somewhere in the sheet you have the data the user keyed in, at least in the file (or are values stored as floats there too?),  

and if you have that data calculation can be done by a function which keeps the info about the 'original digits', and does appr. rounding on the result, 

ok?

(In reply to b. from comment #19)
> or are values stored as floats there too?

This. Calc is not a key logger.

(In reply to Mike Kaganski from comment #20)
> (In reply to b. from comment #19)
> > or are values stored as floats there too?
> 
> This. Calc is not a key logger.

hello @Mike Kaganski, 

thanks for your comment, sorry for objecting, please correct me if i'm wrong, 

from a file saved with ver. 7.1, three cells with values: 

B3: -2
B4: -1,95
B5: 0,05

content.xml: 

... 
<table:table-row table:style-name="ro1">
   <table:table-cell/>
   <table:table-cell office:value-type="float" office:value="-2" calcext:value-type="float">
      <text:p>-2</text:p>
   </table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro1">
   <table:table-cell/>
   <table:table-cell office:value-type="float" office:value="-1.95" calcext:value-type="float">
      <text:p>-1,95</text:p>
   </table:table-cell>
</table:table-row>
<table:table-row table:style-name="ro1">
   <table:table-cell/>
   <table:table-cell office:value-type="float" office:value="0.05" calcext:value-type="float">
      <text:p>0,05</text:p>
   </table:table-cell>
</table:table-row>
... 

trailing zeroes are truncated on save, but the info about the significant decimals keyed in by the user is visible there, besides the values being denominated as 'float' i 'see' pure decimal 'fixed point' fractions there, 

as well as 'value' as as 'text', 

starting with theese values it is technically possible to calculate -0,05 as correct result after 39 additions of 0,05 to -2, instead of -0,0499999999999998, as calc just did, 

even with a 'fuzzy' floating point calculator: 

algorithm 1: 
take -2 
repeat 39 times: 
   [add 0,05 
   round to two decimals] 

algorithm 2: 
take -2
take -1,95
calculate difference
round to two decimals
calculate result times 39
round to two decimals
add to -2
round to two decimals

i accept that you are much deeper in the project and code and have profound knowledge, i see that you did! work to enhance this point, 

but above sample is one of plenty where i - and some other 'simple minded users' - don't understand why calc 'insists in producing obviously wrong results', 

looking at the complaints here and in 'ask' and their long history and how time consuming it is for devs and supporters to deal with theese complaints, and estimating how many other users are irritated without expressing it and how many may turn away from calc or spreadsheets in general ... 

i can't understand why there is such poor success in efforts to produce correct decimal results ... as it would be so easy ... (imho, please correct me if i'm wrong), 

sorry for the long post, i'd like to bring long lasting problems to points where they are either solved or proven as unsolveable, 

to calm down: ex$el is only slightly better in these things, in this test it has only one error in the first 62 lines instead of three (calc), and the first error occurs in line 42 instead of 39 (calc), tested with ver. 2010, winx64, 

but ... calc misses both targets and can claim neither correctness nor ex$el compatibility ...

(In reply to b. from comment #21)
> thanks for your comment, sorry for objecting, please correct me if i'm
> wrong, 

Please don't. There's no need to be sorry for any objecting.

> from a file saved with ver. 7.1, three cells with values: 
> 
> B3: -2
> B4: -1,95
> B5: 0,05
> 
> content.xml: 
> 
> ... 
> ... 
> 
> trailing zeroes are truncated on save, but the info about the significant
> decimals keyed in by the user is visible there, besides the values being
> denominated as 'float' i 'see' pure decimal 'fixed point' fractions there, 

I doubt that this is "info about the significant decimals keyed in by the user". Rather, it's the number rounded to some number of decimals on save, which happens to coincide. Or even possibly a result of smart float-to-string algorithm finding shortest string that round-trips to the same float.

> as well as 'value' as as 'text', 

Of course. When it's output, the output is text of a cell.

> starting with theese values it is technically possible to calculate -0,05 as
> correct result after 39 additions of 0,05 to -2, instead of
> -0,0499999999999998, as calc just did, 

This would require to perform these operations related to obtaining shortest string representation at calculation time, with related unacceptable penalty.

> but ... calc misses both targets and can claim neither correctness nor ex$el
> compatibility ...

In the area of floating-point calculations and related rounding errors, there's no goal in either math correctness in the strong sense (only in the IEEE 754 sense), nor in "Excel compatibility" (it could only be possible if we know exact sequence of the calculations they perform, like if they do it from left-to-right then top-to-bottom, or top-to-bottom then left-to-right, or any other way for a given argument number of SUM). An error bound to rounding errors caused by given correct order of computation is OK; if you can suggest an optimization that gives better precision by re-arranging calculations, without performance loss - it's fine to discuss in a dedicated issue like "Function FOO can give better precision", with analysis of correctness of suggested optimization, and comparison of performance and precision of old and new ways.

(In reply to b. from comment #21)
> to calm down: ex$el is only slightly better in these things, in this test it
> has only one error in the first 62 lines instead of three (calc), and the
> first error occurs in line 42 instead of 39 (calc), tested with ver. 2010,
> winx64, 

It's not "slightly better". It's different. E.g., for case in tdf#129606, Excel produces 32 "wrong" values in first 90 cells, first one being in cell 56; while Calc does not produce a single one (after the fix in the mentioned bug).

And that doesn't mean "Calc is better". As said, they are simply different in how they handle the inherent imprecision in different scenarios.

(In reply to Mike Kaganski from comment #22)

thanks for your fast reply, 

> I doubt that this is "info about the significant decimals keyed in by the
> user". Rather, it's the number rounded to some number of decimals on save,
> which happens to coincide. Or even possibly a result of smart
> float-to-string algorithm finding shortest string that round-trips to the
> same float.

whatever it is - you'll know better than me - it's a valid starting point for the proposed algorithm, 

> if you
> can suggest an optimization that gives better precision by re-arranging
> calculations, without performance loss - it's fine to discuss in a dedicated
> issue like "Function FOO can give better precision", with analysis of
> correctness of suggested optimization, and comparison of performance and
> precision of old and new ways.

this is too high a demand on me, since i am neither c-coder nor performance specialist nor LO developer, 

(besides i tried to give 'my best two cents' in the previous post,) 

an important basic question is whether you are allowed to comment on problems if you cannot do it better yourself, Adolf Hitler said 'NO!', i say 'yes', 

["Only he who can solve a task better is entitled to criticism". - Adolf Hitler Speech on May 1, 1934 in Berlin, in: Max Domarus (Ed.): Reden und Proklamationen, 1932-1945, Vol. 1, p. 379

Source: https://beruhmte-zitate.de/zitate/128459-adolf-hitler-nur-der-ist-zur-kritik-berechtigt-der-eine-aufgab/]

so, if you can accept that even people who are 'idiots' in certain areas are allowed to bring up strange results from these areas and may make - even idiosyncratic - suggestions for solutions, among other things because mankind has mainly developed in this way ... and because you can sometimes free 'specialist idiots' from their narrow-mindedness ... then we can continue talking, 

in the sense of goals and correctness - imho LO / calc should! have goals how to solve calculations, and theese two: 'math-correctness' and 'ex$el-compatibility' i encountered in discussions here again and again,  

'performance' is a dilemma, for 'small tasks' like filling 100 lines with discrete values the 5 nanoseconds? extra computing time needed for step-by-step rounding is certainly the smaller the problem, in contrast to the hours and days we spend explaining the 'necessary errors' to users, in large sheets with thousands of values and calculations the computing speed becomes of course more important, but there the loss due to the immensely complex troubleshooting also increases and the consequences of undiscovered errors can be terribly harmful, 

[in which city do you live? LO is in use at italian military? consider they decide to bomb greece out of europe to become 'the only one largest problem in europe', they use calc for the target calculation and nuke out your city ...]

it is not my choice or decision how LO and calc and 'the developers' deal with these problems, but my urgent recommendation to try to produce satisfied users with correct results and to try to get rid of fp-rounding-errors and to get rid of the miserable compatibility to ex$els errors ... while keeping doors open for users to migrate ... towards LO ... and for that 'more accurate results than ex$el' would be a great invitation ...

P.S. 

tdf#129606: correct, well done, keep going ... 

P.S. II 

i'd suggest to think about general solutions rather than individual bugs ...

(In reply to b. from comment #24)
> whatever it is - you'll know better than me - it's a valid starting point
> for the proposed algorithm, 

No. What is fine at the time of saving, is not OK at the time of performing computations.

No repro on latest master using the repro steps from comment #1 or the sequences from the 2 attached files.  Are there better repro steps, or does this not repro anymore?

Win 10 x64, build id is https://git.libreoffice.org/core/+log/bc817c2fb26ebbfcc7b6868fbcabb13772ddb90e

still repro with procedure from OP: 

-0,09999999999999990000
-0,04999999999999980000
0,00000000000000000000
0,05000000000000030000

@Matt K: did you make the cells wide enough? did you force dsiplay with 16+ decimals? otherwise you'd see rounded values ... 

ver. 2021-02-18_10:36

Version: 7.2.0.0.alpha0+ (x64) / LibreOffice Community
Build ID: 62dff2844b0bf1d1bcb8eb4d6db529ef4a31bee4
CPU threads: 8; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win
Locale: de-DE (de_DE); UI: en-US
Calc: 

P.S. QA-admins reg. green text: why change to 'unconfirmed'? it's clear bug and had been confirmed at least twice ...

Confirmed repro using recent master.  Only able to see behavior in first attachment, not the second.  I had to make the cells wide enough and I forced display to 16 decimals in Calc Options.

I'm also having trouble setting a breakpoint to see why this is happening.  I tried to set a break at approxDiff, but can't get symbols loaded.  When I build core/sc, it builds but the modules aren't loaded by calc -- wondering if the code has changed locations.  Marking NEEDINFO to see if someone can give better code pointers to investigate.

Needinfo is only valid, if a bug report needs further info to be confirmed. It leads to automatic closing in 7 months, so we don't use it in other cases.

@Matt K: 'to see why this is happening' - roundup/roundoff error of factors / divisors used for rounding strike?, while all (sorry, most) '1E+x' values are exact as being integers most '1E-x' values carry deviations, but to different extend, 

while: 
1e-14 is relatively friendly being truncated by '00000001~' resulting in a roundoff deviation of less than -2/256 ULP (~ -1,875/256 ULP), 

1e-10 is already difficult making a 0,5 ULP roundup from '10110111~' resulting in a deviation of approx 1/2 ULP minus 55/256 ULP ~ 73/256 ULP, and 

1e-15 is critical with an approx roundup of 1/2 ULP minus 15/256 ULP -> ~ 113/256 ULP, by rounding up regarding truncation of '10011011~', and 

1e-5 is somewhat catastrophic with an approx roundup of 1/2 ULP minus 4/256 ULP -> ~ 124/256 ULP, by rounding up regarding truncation of '10000100~', 

it's a matter for 'numerologists'? to find a way around those cliffs, 

@all: a simple '=ROUND(A1+0,05;2)' in A2:Ax would make it for this case, is there really no ambition to relieve the user of such simple things (where the users always have problems because, unlike the developers, they only know afterwards! where the pitfalls lay)

@Mike Kaganski: '> return rtl::math::round(0.050000000000000044, 15);' - is that the call to calculate the increment? what about: 
- rounding the step values after adding the increment? 
- rounding them 'harder', not to the max meaningful 15 dec digits (as rounding to '15' is critical, see above), but acc. the amount of decimals digits given by the operands? (two in this case), 
!!! i know !!! that such is more effort, but it's not at 'computation time' but during 'user interaction' and there usually the user is the slow part, 
okok, it may! take some time if you fill a whole column ... but ... imho ... it will be much less than the time we and users throw away with irritations and failed calculations and discussions here, even if you compare to the worldwide accumulated average delay, 

'Or even possibly a result of smart float-to-string algorithm finding shortest string that round-trips to the same float.' - such is available?! it should be used more often ... 

'I doubt that this is "info about the significant decimals keyed in by the user".' - for my silly understanding and acc. occams razor it's somewhat legitim finding the same value you A: keyed in into a cell B: in the saved file defined as value for that cell, that B results of A, if in doubt do some tests, the more often the value changes with what you key in the more likely a relationship becomes, 

'In the area of floating-point calculations and related rounding errors, there's no goal in either math correctness in the strong sense (only in the IEEE 754 sense), nor in "Excel compatibility"' - this is sad and difficult, sad because people should have goals, otherwise they often get lost in space and time or work ineffectively, and difficult because it becomes hard to judge what is good or bad in a project when there are no defined goals, 

'(it could only be possible if we know exact sequence of the calculations they perform, like if they do it from left-to-right then top-to-bottom, or top-to-bottom then left-to-right, or any other way for a given argument number of SUM)' - ex$el calculates: 
- '=Lx+My+Nz': in the order Lx, My, Nz, as written in the formula, no matter where the cells are positioned in the sheet, imho calc does the same, 
- '=sum(Ax:Cy)': top row from left to right then next row, imho different to calc which acts rightmost column bottom up, then nextleft column, i 'assume' oriental influence, and i'm astonished that nobody mentioned that earlier, as it indeed introduces compatibility issues, 
ex$el does not: do any smart sorting of operands reg. magnitude or similar as sometimes somewhere assumed, neither does calc (afaik), 
(above tests with ex$el 2010 ver 14.0.6023.1000 64-bit) 'home and business', 

'No. What is fine at the time of saving, is not OK at the time of performing computations.' - we don't talk about critical mass computations here, we talk about 'UI' and 'filling some cells', once calculated the values may remain ad infinitum, it's much less 'performance impact' compared to users need to define rounded increments and rounded steps by themself and implement them and then have them recalculated with every recalc ... 

reg. 



b.

* A more reliable way is to fill another column with the formula that calculates the difference from what it should be (in the reporter’s example, if you fill column A, the formula would be "=A4-A3-0.05" etc) and make that one wide.
This often shows the error even when the wide filled column itself does not.
* Filling via proper Fill Series command creates the same sort of errors.
* Curiously, selecting 3 or more cells in the filled column and using Fill Series so that the error cell is overwritten eliminates it (the indicator formula column now shows "0").
* Still reproduced on:
Version: 7.6.4.1 (X86_64) / LibreOffice Community
Build ID: 60(Build:1)
CPU threads: 2; OS: Linux 5.4; UI render: default; VCL: x11
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 4:7.6.4-0ubuntu0.18.04.1~lo1
Calc: threaded