an attempt to understand precision of calculations with floating point values, some questions occured

[newbie-02]

hi @all,

there are frequent questions in ‘ask’ and ‘bugs’ about missing accuracy in calculations, which often get the answer ‘it is regarding the limitations and “granularity” of limited length floating point numbers (IEEE 754) used in calc and most other spreadsheets to represent values, and by the small errors that occur when converting from decimal to binary representation, and vice versa, since the two systems partly cover the same values, but often do not’.

in an attempt to understand and visualize which errors evolve from that i’d construct a sheet to ‘re-calculate’ an IEEE 754 number, it’s attached here: questions_about_fp_value_calculations_ori.ods

the questions are in the sheet, in short:

am i right? calc is using [edit] ‘16 bit’ was wrong, i meant ‘16 decimal digit’ [/edit] accuracy for integers, decimal fractions are shortened to 15 digit precision,

but there are more significant bits not evaluated by calc? question B and C in the sheet,

2^53 is rounded down by calc, regardless it has an exact IEEE 754 representaion? D in the sheet,

and a display error for one specific value, question E, i already filed a bug for that: https://bugs.documentfoundation.org/show_bug.cgi?id=131312

my ‘calculator’ holds for plenty samples of values in wikipedia, but not for:

• 1.0000000000000002, the smallest number > 1,

• subnormals - not implemented,

• (Min. normal positive double) - normally it should?

• (Max. Double) - normally it should?

can calc do better or are this the limitations of the sheet?

i’d appreciate if somebody can recheck the sheet, and shed some light, either tell me where i’m wrong, or confirm that there are issues regarding the accuracy produced by calc.

reg.

b.

P.S. add. sheet with irritating results: questions_about_fp_value_calculations_2_ori.ods

[Lupp]

“”: "am i right? calc is using 16 bit accuracy for integers, decimal fractions are shortened to 15 digit precision, "
What do you mean by “16 bit accuracy”? Is it a kind of typo an should be “16 decimal digits”?
Well, Calc doesn’ use any specific number of decimal digits. In fact it uses some converting algorthms for both directions which I don’t know in detail.
Generally there isn’t an exact decimal representation of a IEEE 754 Double mantissa (52 significant bits and the implicit leading 1) if not 52 decimal digits are allowed. Rounding is indispensable.
Since ROUND(log(2^52);2)=15.65 it’s suggesting itself to use 16 decimal digits for the display. It’s reasonable to act on the fact that there is no way to know if the Double representation was exact. So, why worry?
Since 15.65<16 a general conversion to 16 decimals would much more often end with an insignificant digit than welcome, I think.

[Lupp]

“”: "can calc do better or are this the limitations of the sheet? "
Calc is using the floatingpoint capabilities of standard processors. How should there be a “limitation of the sheet” except one either being unavoidable or one being introduced by the code of “the sheet”.
Where you are suspicious an introduced issue might be a bug, you will surely again report it.
In some cases you may see small discrepancies as intentionally accepted (e.g.) due to the need to give naive users the illusion their comparisons for equality make sense.
In case you want to do additional experiments in Calc:

[Lupp]

In case you want to do additional experiments in Calc:
About 2 years ago I made a Calc document comtaining some user code which can exactly show you the actual representation of any numeric cell value in Double bit by bit - and also fill a bitwise defined Double into an actually usabel value. You may use it (e.g.) to perform Double calculations by hand. It’s surely fun!
Since Basic cannot give me direct access to the bit pattern of a variable, I wrote a little program in Pascal and compiled it to a .dll which was in turn called by a function written in Basic.
You can get all that if you want.
(The sheet alone cannot work, of course. You also need the .dll )
BTW: There are uppercase and lowercase letters in western scripts.

[mikekaganski]

It looks such a nonsense when someone tries to “reverse-engineer” LibreOffice, as if it were a closed-source software. When trying to understand it on such a low level, the only way you should use to understand what is actually going on with your calculations is using a debugger. Being open-source, LO allows it. If you don’t, you will not easily find which of your hundreds of intermediate floating-point calculations (to get a single end value that you hope to have exact binary representation) introduced an expected error, or if some end result rounding or display rounding was in effect.

[Lupp]

@mikekaganski: You surely understood:
When I designed what I talked of (sheet and dll) I didn’t try to reverse-engineer Calc. I wanted to see the RAM representation of IEEE 754 ‘Double’ numbers through an atomic microscope, and to be able to manipulate the atoms such numbers consist of. Calc, LibO Basic, and Pascal helped me to equip my laboratory (which you also may call my playground).

[mikekaganski]

@Lupp: I’m sorry for not being precise. My comment was not about your comments, but about OP question. Sorry again.

[Lupp]

Don’t worry. As I said “You surely understood.”
In fact I should have addressed my previous comment to the OQer. I mainly wanted to make definitely clear to him what the offered tools were made for.

[newbie-02]

@Lupp, thks for the bit / digit hint, i edited the question acc.

if you like to share your sheet, .dll and knowledge i’d appreciate that, if you do not want to make it publicly available just send by mail, you’ll find my email in ‘bugs’,

i’m near the limit of my skills, thus i tried to find other people to recheck and help,

[newbie-02]

@Mike2, small hope you’ll ever like what i do, coding and debugging is - at this time - a little above my limits, maybe later,

what i’m trying to do is: find weak points, and show them to the developers, not to blame LO or anyone, but to improve the project, imho that’s neccessary, and sometimes it may help to satisfy your normal users expectations?

just an easy point, plenty of them would appreciate when a number would stay the same on formulate, save-load, and recalc. by F9, not ok for BO20, B25, B37, B40 and B56; and if 2^53-1 would be less than 2^53, less than 2^53+1 … see: add. attachement at the end of the question, acc. to wikipedia i’d expect a granularity of 2, rather than ~10 … and working differences and comparisions … ???

[mikekaganski]

2^53-1 is inside the range that allows contiguous representation of all integers (using 53 bits of mantissa). 2^53 itself is of course technically representable in double, but is outside the 53 bit integer range.

LibreOffice allows to show the whole 53-bit integer range with exact precision. For any integral value outside that range, LibreOffice knows that that value might be a representation of another close integer, and uses usual 15-digit display; thus 2^53-1 shows precisely as 9007199254740991, while 2^53, which should be 9007199254740992, is shown rounded to 9007199254740990 (making strange impression of being smaller). Trying to subtract 2^53-1 from 2^53 will trigger approximate equality (and thus result in 0), while 2^53-2^52-(2^53-1)+2^52 will not trigger approximate equality, and give expected 1.

All this is normal, and we need no help in knowing that. But if you find time for coding and debugging, that would be awesome, and we’d welcome your help.

[Lupp]

@newbie-02 (OT):
The code in Basic is just calling the routines from the library - and there is a bit from my toolbox reverting the order of bits. Everything common sense.
The code in Pascal also is very simple. It was edited with the Lazarus IDE and compiled to a 64-bit windows dll. Tell me if you want the project files.
The sheet simply calls the two functions to either make the ‘Double’ representation of a value visible or to convert a 64-bit-pattern to an ordinary value following the ‘Double’ specification. Nowhere sourcery - and no direct relation to this topic.
Of course the sheet cannot work as long as the Basic code didn’t get inserted the folderpath to the dll.
For me this was part of a “project” to learn about the usage of dll from Basic.
C:\fakepath\dblBitwise_1.dll.jpg
The extension .jpg is a fake to get the file uploaded. Remove it!
dblToBitsAndBack_1.ods

[newbie-02]

@Lupp: hi, thanks, sorry, have seen it late reg. being hidden behind ‘see more comments’ …

nice tool, nice to see what one can do in programming, and nice to see that other people are curious about / look at similar points,

your tool does similar conversion as mine, and gives me similar irritations,

as you are far ahead of my attempts it could be easier for you to understand:

the bit pattern 0 10000110001 11111111 11111111 11111111 11111111 11111111 11111111 1001
evaluates to 2251799813685250,00

while pattern 0 10000110001 11111111 11111111 11111111 11111111 11111111 11111111 1100
evaluates to 2251799813685247,00 (set format to number.2dec, not scientific)

from my ‘simple minded users view’ the second is the bigger value and should not! produce a smaller output,

IEEE 754 Calculator calculates 2251799813685246.3 and 2251799813685247.0

do you understand my irritation, can you help me out? a recheck would be very much appreciated

b.

[mikekaganski]

You don’t need extra complexity (sign/exponent parts) to see some interesting things related to specifics of LibreOffice display. You may use mantissa bits to construct useful values.

1. To get the fractional part of a double’s mantissa (i.e., without implied 1), just use in BR13:

   =SUMPRODUCT(M13:BL13;2^(COLUMN(M13)-COLUMN(M13:BL13)-1))

2. To view it alternatively (as if the mantissa is a whole number, thus not using any rounding):

   =BR13*2^52

3. Interestingly, step 1 will show you “1” for your 52 1s, so you might compare the representation of the last bits with the true 1 using the number from step and

   =2^52

Generally you need to realize that Calc only shows you 15 decimal digits of any floating point value (even if you use more decimals, like 20 in your spreadsheet; but it keeps all bits internally); it often (where makes sense) uses “approximate equality” when comparing/subtracting, because strict equality makes little sense. All that makes Calc UI inappropriate for study of IEEE 754 numbers.

[newbie-02]

i didn’t yet get much more clue about these things, just some points to mention:

Calc only shows you 15 decimal digits of any floating point value

it shows 16 for e.g. 2^53-1,

I have the impression that calc loses more precision than ‘necessary’ by it’s rounding because the last 5 to 7 bits of a float number are often not considered, that might irritate other users as well,

the error ‘E’ from my sheet showing 10,00000000000000 instead of 1,00000000000000 is real and it’s old as @Regina wrote in tdf#131312, is it too complicated to rework it in 12 years?

i tried similar in excel, looks as if it ommits less digits than calc for the result, doesn’t show bug ‘E’, and fails the ‘add. sheet’ from my question more violently than calc, starts violating integer precision somewhere between 2^49 and 2^50,