GNOME Bugzilla – Bug 509965
incorrect rounding of decimal places for numbers, discrepancy to round function
Last modified: 2018-05-22 13:25:06 UTC
Please describe the problem: There is a discrepancy between format cells and the round function, which rounds correctly. "format cells - number - decimal places" rounds wrong in particular cases. Steps to reproduce: 1. enter 0.02965 2. "format cells - number - decimal places" is set to 4 Actual results: 0.0296 appears Expected results: 0.0297 should appear Does this happen every time? Yes Other information:
Created attachment 103010 [details] Minimal example
My personal preference would go to 0.0296 (rounding to the nearest even figure), so I'd say that the round function does not behave as I would like.
In order to fulfill requirements of all types of users, both should be possible: 1. Common method (Symmetric Arithmetic Rounding or Round-Half-Up) 2. Round-to-even method It seems that currently there is a kind of mixture in Gnumeric as shown in the example above. A setting to choose the rounding behavior, where the user can select the method is recommended.
Gnumeric operates in base 2. Any number entered or computed will be rounded to the nearest representable number. Look up the IEEE floating point standard if you need to know the details. The representable number nearest 0.02965 is a tiny bit smaller than the exact value: 0.0296499999999999992728039188705224660225212574005126953125 The correct rounding for that is *down*. (The next representable number is 0.0296500000000000304978264864530501654371619224548339843750 just FYI) So far all well-known spreadsheets agree: Excel, Open Office, etc. We could deliberately mis-round such numbers on the assumption that it probably came from a number with few (base-10) decimals. Excel and OO do that, but that clearly comes with its own set of problems when the assumption is wrong.
Have you got an explanation why displaying 4 decimals (cell B2) gives a different result than the round function to 4 decimals (cell C2) in the attached example?
We do the deliberately-wrong rounding for the ROUND function, see the code below. "fake" refers to that. Note, that I didn't outright close the bug. I am still deliberating whether we should fake-round for display too. static GnmValue * gnumeric_round (GnmFuncEvalInfo *ei, GnmValue const * const *argv) { gnm_float number = value_get_as_float (argv[0]); gnm_float digits = argv[1] ? value_get_as_float (argv[1]) : 0; if (digits >= 0) { if (digits <= GNM_MAX_EXP) { gnm_float p10 = gnm_pow10 ((int)digits); number = gnm_fake_round (number * p10) / p10; } } else { if (digits >= GNM_MIN_EXP) { /* Keep p10 integer. */ gnm_float p10 = gnm_pow10 ((int)-digits); number = gnm_fake_round (number / p10) * p10; } else number = 0; } return value_new_float (number); }
Morten: please don't. One can avoid those screwed up rounding functions or easily provide ones own through a plugin, it is miuch more difficult if the display is being screwed up too!
*** Bug 515297 has been marked as a duplicate of this bug. ***
*** Bug 518128 has been marked as a duplicate of this bug. ***
*** Bug 528864 has been marked as a duplicate of this bug. ***
*** Bug 547039 has been marked as a duplicate of this bug. ***
*** Bug 551796 has been marked as a duplicate of this bug. ***
*** Bug 558747 has been marked as a duplicate of this bug. ***
*** Bug 579934 has been marked as a duplicate of this bug. ***
*** Bug 588392 has been marked as a duplicate of this bug. ***
*** Bug 613557 has been marked as a duplicate of this bug. ***
(In reply to comment #4) > Gnumeric operates in base 2. Any number entered or computed will be rounded > to the nearest representable number. Look up the IEEE floating point > standard if you need to know the details. > > The representable number nearest 0.02965 is a tiny bit smaller than the > exact value: 0.0296499999999999992728039188705224660225212574005126953125 > The correct rounding for that is *down*. (The next representable number > is 0.0296500000000000304978264864530501654371619224548339843750 > just FYI) > > So far all well-known spreadsheets agree: Excel, Open Office, etc. > > We could deliberately mis-round such numbers on the assumption that it > probably came from a number with few (base-10) decimals. Excel and OO > do that, but that clearly comes with its own set of problems when the > assumption is wrong. I understand the difficulty here, but there is a way of solving the problem that presents the numbers that the user would expect to see rather than the computer's internal approximation. I've checked the problem case in Excel 2007 and in Open Office calc 2.3 (released in 2007) and both show 0.02965 as 0.0297 when displayed to 4 decimal places, so perhaps you were testing with older versions. In Gnumeric (Windows 1.9.1 and Linux 1.8.2), 0.02965 shows as 0.0296 at 4 decimal places. Using the +.0 button allows a maximum of 15 decimal places to be displayed, in which case the number is 0.029650000000000 which is good. Using Format Cells the decimal places can be extended further, eg 20 places in which case it displays as 0.02964999999999999927 (as you indicate above). However, since we know that the accuracy of the word size is good for 14 places, but not for 20 (or even 15), there's no point in attempting to represent a number to 15 or 20 places - errors are unavoidable. Therefore why not round to 14 decimal places for the user rather than forcing the user to replace potentially every SUM in their spreadsheet with ROUND(SUM)? (Even 8.47+0.32 doesn't give the expected 8.79 exactly, but 8.790000000000001) In Excel if I create a numeric cell with 20 decimal places and type 1.00000000000000000001 and press Enter it shows 1.00000000000000000000, and it acts similarly at the 15th decimal, but the value of 1.00000000000001 is retained, so Excel's limit is 14 decimal places. This seems to me to be the wisest strategy. You would not be "deliberately mis-rounding" the number but working correctly within the limitations of the computer's numeric representation.
*** Bug 170000 has been marked as a duplicate of this bug. ***
*** Bug 631364 has been marked as a duplicate of this bug. ***
*** Bug 680475 has been marked as a duplicate of this bug. ***
(In reply to comment #6) > We do the deliberately-wrong rounding for the ROUND function, see the > code below. "fake" refers to that. Can we have an option to select the method that suits us best? This could be appropriately located in the Format Cells dialog box, in a similar location to the "Use separator for 1000s" option. Suitable text for the option would be "Round to even".
-- GitLab Migration Automatic Message -- This bug has been migrated to GNOME's GitLab instance and has been closed from further activity. You can subscribe and participate further through the new bug through this link to our GitLab instance: https://gitlab.gnome.org/GNOME/gnumeric/issues/89.