Apache OpenOffice (AOO) Bugzilla – Issue 18303
Missing Documentation about Internal Number Representation
Last modified: 2013-02-07 22:38:10 UTC
The supplied online help gives good hints about the number formats in Calc, but almost nothing about the internal number representation. So what is the internal precision ? Successive numbers close to 1.0 are separated by small quantities, more or less 10^(-14) (that is, 1E-14). This means that Calc uses "double" real numbers (8 bytes per number) but not "extended" (10 bytes). Concerning integers, things become unexact from 2^48 since 2^48-(2^48-1)=1 while (2^48+1)-2^48=0. However, 2^48+1 doesn't display like 2^48 because of the binary-to-decimal conversion and also of the standard display format, which tries to show integers "as they are", up to 10^15 more or less. Such informations (about the exact numbers you can represent within Calc) should appear somewhere in the help. Please look at issues #7410, #15559, #1182 which are related. Below in french. L'aide en ligne donne plein de détails concernant les formats d'affichage des nombres dans Calc mais presque rien sur la représentation interne. Quelle est donc la précision des nombres dans Calc ? En fait, au voisinage du nombre 1 l'écart entre deux nombres jugés différents est de l'ordre de 10^(-14) (1E-14 si on veut). Autrement dit, Calc utilise le type "double" mais pas le type "extended". En ce qui concerne les nombres entiers, ça s'écarte autour de 2^48 en ce sens que 2^48-(2^48-1)=1 et (2^48+1)-2^48=0. Cependant 2^48+1 s'affiche différemment de 2^48 à cause de la conversion décimale et du format d'affichage standard qui tente de garder une représentation entière tant que possible (jusqu'à 10^15 environ). C'est ainsi que de telles informations (sur les nombres représentables sans erreur) devraient apparaître dans l'aide en ligne. Voyez les issues #7410, #15559, #1182 qui sont sur un sujet voisin.
Hi Bettina, 1 4 u. Frank
Some details about Double Precision Floating Point Arithmetic. The notes which follow could serve as a base for online help enhancement. A word of caution: Calc does a bit more than IEEE 754. Very small numbers seem to be considered as "true zero" (so there is no "underflow"). ************************************************************** The IEEE 754 double precision floating point standard representation requires a 64 bit word, which may be represented as numbered from 0 to 63, left to right. The first bit is the sign bit, S, the next eleven bits are the exponent bits, 'E', and the final 52 bits are the fraction 'F': S EEEEEEEEEEE FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF 0 1 11 12 63 The value V represented by the word may be determined as follows: * If E=2047 and F is nonzero, then V=NaN ("Not a number") * If E=2047 and F is zero and S is 1, then V=-Infinity * If E=2047 and F is zero and S is 0, then V=Infinity * If 0<E<2047 then V=(-1)^S * 2^(E-1023) * (1.F) where "1.F" is intended to represent the binary number created by prefixing F with an implicit leading 1 and a binary point. * If E=0 and F is nonzero, then V=(-1)^S * 2^(-1022) * (0.F) These are "unnormalized" values. * If E=0 and F is zero and S is 1, then V=-0 * If E=0 and F is zero and S is 0, then V=0 The magnitude of the relative error in a number is bounded by 2^{-53} = 1.11... x 10^{-16}. This means we get almost 16 decimal digit precision. (The largest possible mantissa is M = 2^{53} = 9.007...x10^15, which has 15+ digits of precision.) The largest positive number that can be stored is 1.11111....11111 (binary) x 2^{1023} = 1.797693... x 10^{308}. Notice that 1.11111....11111 (binary) = 2 - 2^{-52}. Also note that log_{10}(largest) = 308.2547... The smallest positive number is 1.00000...00000 (binary) x 2^{-1022} = 2.225074... x 10^{-308}. Note that log_{10}(smallest) = -307.6526... References: http://www.psc.edu/general/software/packages/ieee/ieee.html http://www.scri.fsu.edu/~jac/MAD3401/Backgrnd/ieee-dbl.html ANSI/IEEE Standard 754-1985, Standard for Binary Floating Point Arithmetic
About Calc's display assumptions (this is intended for online help) ******************************************************************* Please take into account the fact that, sometimes, Calc /displays/ zero (0) for non-zero numbers. Such situation arises for very small numbers (typically less than 1E-15) in standard format. So the test for nullity (e.g. =IF(A1=0;"A";"B") is, for such (non-integer) numbers, irrelevant. If you want to detect "almost zero" numbers, place a small threshold within some cell, and compare the absolute value of your number with that threshold.
OpenOffice.org Issue Tracker - Feedback Request. The Issue you raised has the status 'New' pending further action, but has not been updated within the last 4 years. Please consider re-testing with one of the latest versions of OOo, as the problem(s) may have already been addressed. Either use the recent stable version: http://download.openoffice.org/index.html or consider trying the new OOo 3 BETA (still in testing): http://download.openoffice.org/3.0beta/ Please report back the outcome so this Issue may be Closed or Progressed as necessary - otherwise it may be Resolved as Invalid in the future. You may also wish to search for (and note) any duplicates of this Issue that may have advanced further by checking the Issue Tracker: http://www.openoffice.org/issues/query.cgi Many thanks, Andrew Cleaning-up and Closing old Issues as part of: ~ The Grand Bug Squash, pre v3 ~ http://marketing.openoffice.org/3.0/announcementbeta.html
To grep the issues easier via "requirements" I put the issues currently lying on my owner to the owner "requirements".