Frequently asked questions - Calc: Accuracy problem

    From The Document Foundation Wiki
    < Faq‎ | Calc
    Other languages:

    Inherent Accuracy Problem

    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, many decimal numbers - including as simple as 0.1 - cannot be precisely represented in Calc (which uses 64-bit double-precision numbers internally). Calculations with those numbers necessarily results in rounding errors, and those accumulate with every calculation. This is not a bug, but is expected and currently unavoidable without using complex calculations in software, which would incur inappropriate performance penalties, and thus is out of question. Users need to account for that, and use rounding and comparisons with epsilon as necessary.

    An example:

    1 31000.99
    2 32000.12
    3 =A1-A2

    This will result in -999.129999999997 in A3, instead of expected -999.13 (you might need to increase shown decimal places in cell format to see this).

    Due to the specific of time representation in Calc, this also applies to all calculations involving times. E.g., in the following example, the cells A1 and A2 show the date+time data as entered (in ISO 8601 format):

    1 2020-04-13 12:18:00
    2 2020-04-13 12:08:00
    3 =A1-A2

    Cell A3 will show 00:09:59.999999 instead of expected 00:10:00.000000 if formatted using [HH]:MM:SS.000000 format string. This happens despite only whole numbers of hours and minutes were used, because internally, any time is a fraction of a day, 12:00 (noon) being represented as 0.5. The data in A1 is represented internally as 43934.5125, and in A2 as 43934.5055555555591126903891563 (which is not exact representation of the entered datetime, which would be 43934.505555555555555555...). Their subtraction results in 0.00694444443287037, a value slightly less than expected 0.00694444444444..., which is 10 minutes.

    Order of magnitude between operands

    The maximum order of magnitude between operands is lower than 10^15 (1015). This is not related to the display format for the cell.

    For instance, when performing 1E+9 + 1E-7 you might not get the result you are expecting, since 9 - (-7) = 16 > 15. On the other hand, performing 1E+8 + 1E-6 will indeed result in 100000000.000001 (you might need to increase shown decimal places in cell format to see this).

    See Also

    Related bookmarks collected by erAck