[Semibug] internal number storage in libre office calc [was: LibreOffice is summing incorrectly]

[CAREY SCHUG]

(1) the home page for gnucalc implies there is a way to force single precision instead of double precision, which would cut space for numbers in half, but since the numbers themselves are not all of the space, I don't know how much that would help.  I have made errors, like deleting a large block without realizing it, and not discover the error till days later, after several saves, so we can't just go back..back...back, and I make a backup file every few days.  OK, my spreadsheet is only 1.3 MB (due to tricks I describe below, it used to be bigger), but still i'd rather it be smaller.  single precision calculation is faster too.  Still, every 3-6 months i archive the older data to a separate file (probably no real point, but I entered the data, hate to just delete it forever).

all I can find in libre office help is to force DISPLAYING single precision, not storing internally.  Maybe since formula, and original typed numbers, and formatting codes are all stored, just changing the actual binary numbers would not save enough bytes to bother with, and I should give this up.

Experiment: if I enter a1=2.2 and b2=1.1 they are stored as 3 8-bit characters each, not as a floating point number...or both as character and number? If I change the formatting of a1 to be text, it still shows 2.2, but now left aligned as a text field would be.  THEN, if I enter =a1/b1 into cell c1 it shows 2!! so even though a1 is not defined as a number it still decodes it for the calculation.  I could swear that many years ago it would have resulted in a formatting error.  If I then change the formatting for cell c1 to be text, it shows 2, left aligned like a text field, not the formula I typed in. In the entry field at the top, it still shows the formula!!  I'm sorry, IMHO, this is all just wrong.  I don't want calc to "fix" my errors, if I made an error, maybe their "fix" is not actually what I intended, better to let me know something is wrong.

when I entered the formula into c1, it should have said "invalid" because I can't divide an alpha field, and if I hadn't changed the format for a1, then when I changed the format for c1, it should have displayed "=a1/a2"

QUESTION: if I enter into c1 "=a1/b1" then extend it down to c500, will the formulas for each be stored, so the last one contains "=a500/b500" as well as the double precision binary value calculated, or is there some internal formula that says "this is like the line above adjusted for line number"?   

I am basically entering numbers in the first ~40 columns, and periodically shifting ~20 at a time to the right to make room for the next data.  There are some simple formula on the order of "if this is blank and that is not, copy that number to here" or "if that is blank, display a blank, else calculate something divided by that".  I display counts of non-blank and an error if the number of non-blanks is different than the number of non-numeric (meaning I typed in a non-number where it should be a number).  I also calculate an average based upon the numbers all the way to the right.  To make the spreadsheet smaller, I will sometimes select and copy everything beyond my current entry area, then paste back special, ONLY numbers (no formula).

(2) thank you for the link.  so dates are integer days from epoch start and times fraction of 1 day, so if I can figure out how to use single precision for everything, it should make my spreadsheet smaller and faster.  assuming libre office has that choice.  as I said, the splash page for gnucalc implied that could be changed.

(3) perhaps I could have all integers (100 times bigger than the desired number), and shift the decimal while displaying, but if I have to learn to type in everything, that is a LOT more work (mostly i now enter numbers like 5 or 3 or 2.1 or .12 so typing 500, 300, 210 and 12 would be more typing) and an even longer learning curve...plus if I forget and type in the extra zeros when setting up a payment online, could wipe out my bank account.  I once bid on ebay entering 500 like would for Paypal, confused that ebay starts with dollars.  I was high bidder immediately at 15 cents or something, ebay would not let me retract, so I sweated till the auction close that nobody would bid up a little at a time looking for my high bid).  I think I eventually won for 7-something so did not suffer badly.

<pre>--Carey</pre>

> On 05/22/2024 11:06 PM CDT Ron / BCLUG <admin at bclug.ca> wrote:
> 
>  
> 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
> 
> _______________________________________________
> Semibug mailing list
> Semibug at lists.nycbug.org
> https://lists.nycbug.org:8443/mailman/listinfo/semibug