Accuracy in LibreOffice Calc

[PanicJacob]

Hello LibreOffice Community,

First off, I want to say thank you to you all who keep this project going. As a college student I have benefited greatly from using this software to complete assignments without the use of expensive software. I very much hope to one day give back to this project.

On to my question. I was recently asked by my current PI on what the scientific(?) accuracy of Calc is, specifically in regards to the linear regression formulation when formatting a chart for a trendline? This was also in comparison to OriginPro, a research-oriented program. I’m not sure if I’m asking the right question in regards to how accurate one is compared to the other, or if the use case is even remotely similar.

Additionally, how did you come to the determination of the accuracy? I did a few Google searches and the main accuracy point I could find was the max number allowed in a cell (nothing greater than 10^308) as well as the max number of fractional digits OriginPro is capable of (15 digits).

I apologize if this has already been answered. Thank you for your time!

[mikekaganski]

https://wiki.documentfoundation.org/Faq/Calc/Accuracy

[Zizi64]

The LO Calc uses double precision floating-point format numbers:

Double-precision floating-point format

[Lupp]

In addition to the already given facts:
There can’t be (imo) a reliable round-trip stability concerning the conversion between IEEE 754 Double and decimal with 15 digits mantissa and up to 3 digits scaling exponent. Anyway the conversion takes time. Nonetheless the second format is used for the persistent representation of documents (files following ODF specifications).
Probably @mikekaganski can give valid reasons for what not the HEX representation bitwise mapping the mentioned dyadic standard was chosen.

[mikekaganski]

@Lupp

There can’t be (imo) a reliable round-trip stability concerning the conversion between IEEE 754 Double and decimal with 15 digits mantissa and up to 3 digits scaling exponent.

True. For reliable round-trip of a 64-bit IEEE 754 binary number, 17 decimals are required.

@Lupp

Probably @mikekaganski can give valid reasons for what not the HEX representation bitwise mapping the mentioned dyadic standard was chosen.

I don’t quite see where “decimal with 15 digits mantissa and up to 3 digits scaling exponent” is defined as something chosen in a standard

[Lupp]

@mikekaganski

For reliable round-trip of a 64-bit IEEE 754 binary number, 17 decimals are required.

Ok. .

@mikekaganski

I don’t quite see where “decimal with 15 digits mantissa and up to 3 digits scaling exponent” is defined

I also don’t. Sorry! I took that for granted because I found it this way in all the cases where I inspected the content.xml of a document containing numbers without a restriction to the accuracy. Actually ODF shouldn 't be expected to specify the representation of numbers for an opened document. On the other hand numbers mentioned in ODF documents I know (superficially) are described as texts. In ODF 1.3 part 4 I find numbers defined syntactically among the Basic Expressions (as already in Algol 60) , and there is no limitation to the tally of digits, of course. But as soon as I enter a formula like =1.234567890123456789E+13 the value is reduced to 15 significant decimal digits for the display without notion and also saved this way to the file(if).
If I edit such a file and enter the mentioned number there, it’s accepted on reload, but yields the same result, of course. (I didn’t study the XML Schema Part2 Section 3.2.4 to which the “float” explanation of ODF 1.3 part 4 refers.)
OK. I won’t study the logic of ODF and XML now to every detail…

• Isn’t there any specification then concerning the “persistent accuracy” (file) of numbers?
• Even not binding to the current IEEE754 hardware standard, it is a de-facto standard, and the next one will most likely be similar. ODF could store numbers as “bitmap” using HEX notation e.g. prefixing a formatsign : Read the 64 bits as 16 hex digits simply.

[mikekaganski]

@Lupp

• Isn’t there any specification then concerning the “persistent accuracy” (file) of numbers?

If you mean in ODF, then I believe there would be none. I suppose ODF doesn’t specify these things; in the end, it should not rely on IEEE754 or some other specific machine number representation.

@Lupp

ODF could store numbers as “bitmap” using HEX notation e.g. prefixing a formatsign : Read the 64 bits as 16 hex digits simply.

Yes, but unlikely. It would require a next revision to allow that (the number representation as decimal (of unspecified precision) is part of the standard, which references XML general types), and then every file making use of that feature would be backward incompatible. Until there is evidence that that would give real benefit, and that is impossible to get that benefit by simply using enough decimals, and that would give real difference at load/save performance, I’d say it’s not worth it.

[PanicJacob]

Thank you all very much for your answers. I appreciate you all taking the time to answer my question. I have selected a solution, but also hope that others read the rest of the comments when asking about the accuracy as I did. I learned a good deal!