Unexpected decimal precision error in Calc

I stumbled upon an unexpected precision error in Calc. I understand that floating point numbers are used internally, and can sometimes cause incorrect results in low-significance digits, but this is a really simple case and it surprised me.
I’m starting with a CSV file, which contains a formula. I simplified the issue down to a short 3 line file; the following is the entire file:


Then open that .csv file with LO Calc, checking the “Evaluate formulas” checkbox on the import wizard.
The result in cell A3, which should be simply 0.63, comes up as 0.630000000000001.

I am surprised that such a simple subtraction would generate an error like this; although I expect that someone will probably tell me that floating point number do things like that now and then.

But I would then point out the fact that if you create a new spreadsheet and enter the exact same numbers in cells A1 and A2, and enter the same formula in A3, then Calc displays the correct result in A3, “0.63”. In other words, the behavior is different, despite using the exact same data and formula, depending whether you import from a CSV file, or create the spreadsheet manually by typing in the data into Calc.

I’m not sure if I can call this a “bug”, but I will call it a strange quirk, and a bit of an annoyance.
I am running version

Help supplies a bit of the answer, Calculation Accuracy

This has more of an explanation, Numeric precision in Microsoft Excel - Wikipedia

You can hide the problem by choosing a suitable format. Do you really need 15 digit precision? If you do then you should be using Python or looking at specialist programs.

The comments about calculation accuracy are sort of what I expected to hear.

However, I’m still wondering why there is a discrepancy in the behavior of an imported CSV file, as opposed to typing in the formula by hand. In the latter case, Calc displays exactly 0.63. No cell formatting has been applied in either case. So apparently when you type in a formula into a cell that has no prior formatting, by default Calc seems to “know” that it only needs to display 2 digits of precision.

It is definitely a behavioral difference that depends on whether it’s a CSV import, or manually typed data/formula. Does anyone have any thoughts on that?

A csv contains no formatting. The import dialogue adds some for special numbers and dates but everything else will be as in the original, unformatted

A new, initially empty spreadsheet contains no formatting either. You just type a number or formula into an empty cell. Why would the imported file be displayed differently from the manually created file, when neither of them has had any formatting applied?

This could be an enhancement request. Every cell in every spreadsheet has some (even if default) formatting; the difference you describe shows independent evolution of rules applied on manual entry and on CSV import. It looks reasonable to try to unify these.

1 Like

Thanks Mike. Done.