Issue 118342 - Irregular rounding displays where rounding is not in effect
Summary: Irregular rounding displays where rounding is not in effect
Alias: None
Product: Calc
Classification: Application
Component: editing (show other issues)
Version: OOo 3.3
Hardware: PC Windows XP
: P3 Normal (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
Depends on:
Reported: 2011-07-26 13:49 UTC by jimimaseye
Modified: 2013-03-29 15:11 UTC (History)
2 users (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: 3.4.1
Developer Difficulty: ---

single sheet, 18 row demonstration of the problem (16.50 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-07-26 13:49 UTC, jimimaseye
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description jimimaseye 2011-07-26 13:49:29 UTC
Created attachment 76729 [details]
single sheet, 18 row demonstration of the problem

Simple formulas including addition and subtraction of near whole integers (exceptions only down to 2 decimal places) and yet the sheet displays 'nth decimal point E values for a number that SHOULD be Zero.  (eg, a formula equating to 97.29+33.92-131.21 = -5.6843418860808E-013  )

As demonstrated in the attached sheet.

I regularly find myself modifying cells to include a ROUND() function to overcome this irregularity on many sheets as I come across them.  But this should not be necessary.

Problem does NOT apply in OOO v3.1.
Comment 1 jimimaseye 2011-07-28 13:26:34 UTC
Having looked into this further, I see that this has been introduced at version 3.3

Here are the development notes explaining the introduction of the feature to which you refer:

Menu Tools > Options > Calc > Calculate, Limit decimal for general number format.

Where this feature explains WHY the display performs the way it does (in particular the ossue with the figure being less than 0.0001), I still maintain that something is wrong when software on a computer cannot calculate 56.25+35.96-92.21 and conclude its something that ISNT zero. (A simple calculation that a child can do).  Where I acknowledge that in this world of BINARY calculations we would have to deal with minute decimal fractions to a certain degree, it is incumbent on software creators to tailor for this problem.

However, I have just tested the exact scenario in Excel and that too also shows SIMILAR properties in its results (I say similar because it doesnt calculate the same decimal figure!).

Given that excel also does a similar, and that clearly OOo have moved towards performing the same way as Excel (ouch!) in a form of 'standardisation' then I guess I will concede to the fact that this is the way it is to be if I choose to go with OOo3.3 or above (which I am not prepared to do). It does mean, of course, that installing and implementing any new OOo onto machines means I have to go in to the options and IMMEDIATELY CHANGE THE DEFAULTS (using this option) to prevent this unsightly mess happening every time a spreadsheet is opened. (Something is wrong if you have to change the defaults straight away before even opening a sheet for the first time).

Therefore my suggestion would be to rather than have column width determine what level of decimalisation can be shown, actually have the option set AS DEFAULT to a high number of places (say 10) which would allow for these inabilities for computers to compute perfectly by still showing Zero when it clearly should be, and yet give users the opportunity to change/format the cell IF they know that the numbers they are dealing within that cell are REALLY true low fraction numbers (and not 'Zero errors').

Im sure there are other considerationsa to make in reviewing my suggestions and this issue which i will gladly listen and be open to, but SOMETHING should be done as it JUST ISNT RIGHT that every spread sheet that has a calculation resulting in zero gets automatically filled with the scientific notation is displayed.  (The business workplace likes clear and clean readable numbers).
Comment 2 Oliver-Rainer Wittmann 2012-06-13 12:29:12 UTC
getting rid of value "enhancement" for field "severity".
For enhancement the field "issue type" shall be used.
Comment 3 Edwin Sharp 2013-03-29 15:11:48 UTC
What a strange bug!
Excel 2010 shows cells F19 till F29 as -5.68434e-13