https://bugs.documentfoundation.org/show_bug.cgi?id=160221
Bug ID: 160221
Summary: Precision error display on CSV import differs from
display of manually created spreadsheet
Product: LibreOffice
Version: 7.6.5.2 release
Hardware: All
OS: All
Status: UNCONFIRMED
Severity: enhancement
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Created attachment 193131
--> https://bugs.documentfoundation.org/attachment.cgi?id=193131&action=edit
screenshot of the described issue
This is an enhancement request. I think there should be consistency in the
display of the result of a subtraction operation, regardless of the method of
creating the spreadsheet (manually vs. CSV import).
Some very simple math operations may have a result that is "wrong" due to
floating point inaccuracies in extremely low order digits (i.e., in the 15th or
greater significant digit of a decimal number). This is to be expected, due to
the inherent precision capability of floating point math.
However, the behavior may be different depending on whether a spreadsheet is
created manually by typing data into a new (empty) spreadsheet, or if it is
imported from a .CSV file.
Here is my example that demonstrates this issue. The following 3 lines
represent the entire file:
11.92
11.29
=A1-A2
If the lines above are entered into a new spreadsheet, then the result that
appears in cell A3 is 0.63 (as one would expect).
But if those same 3 lines are in a .CSV file, and you import that file into
Calc using the Text Import wizard (be sure to check the "Evaluate Formulas"
checkbox), then the field width is expanded and a full 15 digits of precision
are displayed, with the result in cell A3 being "0.630000000000001".
I would like for the CVS import method to follow the same rules as the manually
created spreadsheet case, and only display the meaningful digits (just 0.63).
Note that in both cases, no cell formatting has been done, and no changes have
been made to the column width.
Also note that this is a common occurrence when subtracting numbers that have 2
digits after the decimal point. A .csv file containing financial data is a
VERY common application of a spreadsheet; numbers representing dollars & cents
always have 2 digits after the decimal. So when the user's spreadsheet
consists mostly of simple math on tables of dollars.cents values, getting the
anomalous math results is distracting and annoying, and it forces you to have
to manually change the formatting.
I would also add that if the user has not applied any formatting change to a
cell (presumably it has some kind of default formatting), then why should
Calc's default formatting cause it to display 15+ significant digits, when it
is a well-known fact that numeric values are not reliable beyond 14 or so
digits?
In my opinion, the default format of a cell containing a math formula should
not display low-order digits of a decimal number beyond 14 significant digits.
Unless the user has explicitly changed the format to display more digits,
there's no reason to show 15 or more digits when we KNOW that the lowest order
digit(s) are likely to be garbage.
At a minimum though, I'd like to see consistency between the CSV import case
and the manually created spreadsheet case.
--
You are receiving this mail because:
You are the assignee for the bug.
