Ask Your Question

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

asked 2020-03-29 21:06:39 +0200

newbie-02 gravatar image

updated 2020-08-07 02:50:23 +0200

Alex Kemp gravatar image

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: C:\fakepath\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:

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.



P.S. add. sheet with irritating results: C:\fakepath\questions_about_fp_value_calculations_2_ori.ods

edit retag flag offensive close merge delete



"": "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 ...(more)

Lupp gravatar imageLupp ( 2020-03-30 01:09:38 +0200 )edit

"": "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 gravatar imageLupp ( 2020-03-30 01:24:33 +0200 )edit

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.

Lupp gravatar imageLupp ( 2020-03-30 01:29:51 +0200 )edit

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.

Mike Kaganski gravatar imageMike Kaganski ( 2020-03-30 08:54:37 +0200 )edit

@Mike Kaganski: 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).

Lupp gravatar imageLupp ( 2020-03-30 11:21:35 +0200 )edit

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

Mike Kaganski gravatar imageMike Kaganski ( 2020-03-30 11:46:07 +0200 )edit

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.

Lupp gravatar imageLupp ( 2020-03-30 12:13:14 +0200 )edit

@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 gravatar imagenewbie-02 ( 2020-03-30 18:15:47 +0200 )edit

@Mike, 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 ... ???

newbie-02 gravatar imagenewbie-02 ( 2020-03-30 19:48:01 +0200 )edit

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 ...(more)

Mike Kaganski gravatar imageMike Kaganski ( 2020-03-30 20:02:58 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-03-30 18:29:51 +0200

updated 2020-03-30 18:36:01 +0200

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:


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


  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


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.

edit flag offensive delete link more


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,

newbie-02 gravatar imagenewbie-02 ( 2020-07-26 17:50:11 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-03-29 21:06:39 +0200

Seen: 91 times

Last updated: Mar 30