[Semibug] LibreOffice is summing incorrectly

[Ron / BCLUG]

CAREY SCHUG wrote on 2024-05-22 14:27:

> I typed 4.73 into a cell, it was actually stored that way

> LibreOffice Calc (which uses 64-bit double-precision numbers 
> internally)

https://help.libreoffice.org/latest/en-US/text/scalc/01/calculation_accuracy.html


> never have more than 5 (actually 4.5, meaning 199.99 to .01) 
> significant digits, so single precision could make the spreadsheet a 
> LOT smaller.

Since all numbers are stored as 64 bit double-precision, it doesn't look
like there's a way to reduce spreadsheet size by fiddling with storage.


> If I define a cell as ONLY a date OR a time, will it insist on
> storing it the internal clock format, which requires double
> precision?

Yes. From the link above:

> internally, any time is a fraction of a day, 12:00 (noon) being 
> represented as 0.5.


> Or better yet, store (the non date/time) as 100x integers, meaning
> .02 would be stored as 2, and 5 would be stored as 500.

If you input numbers as n*100 and display them back as n÷100, that might
work nicely. I've heard of that technique used in financial transactions.


> For displaying, I would like to DISPLAY in non-scientific format,
> but with a limited number of significant digits

Number formatting supports custom formats, so that's do-able.


> Inherent Accuracy Problem
> 
> LibreOffice Calc, just like most other spreadsheet software, uses
> floating-point math capabilities available on hardware. Given that
> most contemporary hardware uses binary floating-point arithmetic with
> limited precision defined in IEEE 754 standard, many decimal numbers
> - including as simple as 0.1 - cannot be precisely represented in
> LibreOffice Calc (which uses 64-bit double-precision numbers
> internally).


That link is pretty interesting, and I didn't realize time formats were 
susceptible to rounding issues; I expected them to be stored in Unix 
epoch format.


rb