GNOME Bugzilla – Bug 684914
Autofill of sequential numbers with decimals malfunctioning
Last modified: 2012-09-27 21:36:16 UTC
Created attachment 225235 [details] Screencapture of the erroneous output When using autofill to create a range of decimal numbers the incrementation between each decimal becomes corrupted after a variable number of boxes. i.e. I enter 6.1 into A1, 6.2 into A2, and attempt to use autofill (highlight both cells, click on bottom right and drag down) to populate cells until 8.0 is reached. After 7.1 comes 7.20000000000001. I have no idea why a value of 1x10^-14 was added to the increment.
It's clear what happened: 0.1 cannot be represented precisely in base 2 and the resulting rounding error accumulated and over time became big enough to be visible. It's less clear what to do about this.
Morten, Thank you for your follow up. I suppose that makes sense. However, there has to be a way to handle this sort of issue. I'm far from a coder myself, but it seems to me that many other spreadsheet programs handle this gracefully (or just hide their inaccuracies). I tried this out in LibreOffice's Calc and it worked just fine. Let me know if there is anything I can do to help further. Thanks again, Matt
Yes, some other applications hide this by sacrificing precision. They run into other problems elsewhere.
Interesting... Well I can do my best to ignore it for now. If I change the formatting of the cells to display just the desired precision its ok. I've been using Gnumeric for years and don't recall this issue in the past, not sure what changed in the codebase that's causing it to display this way now. Maybe a quick fix is simply to change the default formatting on cells to display some present precision. That or I have noticed that the column width expands automatically beyond the default as I autofill past the point where the error begins and the error is then visible. If I narrow the column width suddenly the error is hidden again. Thanks for the follow up, Matt
This issue has been around for a long while. See bug #528864 for example. Note that this issue would never appear with integers or with deltas of 0.5 or 0.25 etc, since those are exactly representable in base 2. *** This bug has been marked as a duplicate of bug 528864 ***
Andreas, I am of two minds here. This bug is about autofill only. We clearly have users who use autofill with "nice"[*] numbers. Do we _ever_ expect to see autofill with numbers that are not "nice", i.e., number that were really meant to be accurate to the last bit? What's the use-case for autofilling that kind of numbers? [*] A poorly defined property, probably implying a short base-10 representation.
Morten, note that I marked this as a duplicate of bug #528864 which describes exactly the same situations (autofilling). I know that you had marked that bug as a duplicate of bug #509965 which is the general number representation issue. Personally I think that in the case of autofilling this is fixable (and should probably be fixed.) If delta is not an integer we basically should scale the numbers in question so that an appropriately rounded delta is an integer, extend the number and scale back. Personally I find it much more serious that data->fill->series exhibits the same behaviour.
Note that the data->fill->series behaviour is in fact much stranger. Try to fill a column starting at 6.1 with a delta of +1. We are drifting in both directions...??