45.95 - 46.57 = -0.619999999999997 (should be -0.62 exactly; no rounding)

NOYB · August 23, 2025, 11:03pm

Open new calc spreadsheet
Enter this formula in cell A1 =45.95-46.57
Cell A1 displays -0.62 as expected.

But formulas that reference cell A1 get -0.619999999999997

45.95-46.57 equals exactly -0.62
There should be no rounding error.

Cut and paste cell A1 to B1 as values only
Cell B1 displays -0.619999999999997

Version: 25.2.5.2 (X86_64) / LibreOffice Community
Build ID: 03d19516eb2e1dd5d4ccd751a0d6f35f35e08022
CPU threads: 8; OS: Windows 11 X86_64 (10.0 build 26100); UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

Zizi64 · August 24, 2025, 7:03am

It is a generic “problem” of the different numerical systems.

1/3(dec) equals an endless decimal fraction in the decimal system: 0.333333333…(dec)
1/10 is a simple number (0.1) in the decimal system, but it is an endless binary fraction:
1/10(dec) = 0.1(dec) = 0.00011001100110011…(bin) = 1/16(dec)+1/32(dec)+1/256(dec)+1/512(dec)…

Sometimes the operands or the result cannot be depicted with a simple, short number in a binary based computer.
Use the function Round to rounding the results to the desired decimal places.

koyotak · August 24, 2025, 10:01am

supplementary:
-0.62 is the analog calculated result, whereas -0.61999… is the digitally calculated result by any microcontroller. Both are correct, which every programmer needs to know. The binary phenomenon occurs when switching from the positive quadrant to the negative one, because the negative sign doesn’t exist in binary and is instead preceded by a “1” as a pseudo MSB (most significant bit). This pseudo MSB is not a numeric value like all subsequent binary digits “0” and “1.” Theoretically, one could add the “difference value” to the analog result if one knew how small it would be: 1^(-12)…1^(-1024).

In the digital system, only addition is possible by comparing the two bits and carry over to the more significant bit. Subtraction is performed as the addition of a negative number. Be aware that the analog world, the digital-electrical world, and the binary-programmatic world lead to translation errors.

koyotak · August 24, 2025, 10:36am

Mathematically, =1/3 is the final solution to an analog calculation, while 0.333… only returns an approximate number: ≈0.333, which also only represents =0.(overlined)3.

I remember a technical article that suggested a different approach: instead of dividing n by 5, which leads to just such an “error” because no microcontroller can divide analogically, one should divide the formula by n × 2 / 10, the doubling is performed as an addition to itself, and this result is shifted bitwise by one bit position to the “right.” I was astonished by this; I must have been asleep at the technical college!

ajlittoz · August 24, 2025, 10:44am

And this “trick” does not change anything (apart perhaps speed) because 1/10 = 1/2 × 1/5 and 1/5 cannot be represented exactly in base 1/2 because 2 and 5 (which are primes) are relatively primes to each other (I should write 1/2 and 1/5 but the result is the same). Fractional bases lead to this phenomenon unless you convert into bases which are integer multiples of each other.

EDIT:
The main problem in the kind of question like this one is a lack of education. Computers are now present everywhere and fundamentals are no longer taught. Thus lay users take for granted (considering the incredible achievements made possible) that computers always provide exact arithmetic results. Unfortunately, they are quite dumb, only being able to deal with signed integers. All other types of numbers (fractions in Q, reals in R, complex in C) are simulated as approximate integer values. Note that base 1/2 (and similar) is an “artefact” caused by moving the “decimal point” from right to extreme left. This shift brings also its share of problems.

When computers began to be ubiquitous in the '70s and '80s, every “technical” training program had a module about numerical analysis applied to computer calculations. Nowadays, this kind of module is deemed unnecessary and graduates have no idea about the tons of issues resulting from bad computation methods. And I don’t speak of non-technical average Joe.

The most terrifying time bomb is that even computer students are not made aware of the mathematical issue because emphasis is now put on GUI, internet or object theory. After graduation, they may be hired to create technical or business suites without consideration for the intrinsic traps.

Please, forgive my rant (it is probably off-topic).

karolus · August 24, 2025, 11:05am

btw.
If we calculate the circumference of a circle with a diameter of 10,000.00 km, we obtain an error of ~ 63 nanometers when rounding PI to 15 decimal places, compared to a much more accurate value for PI.
This corresponds to approximately the distance between 200 water molecules placed side by side.

from decimal import Decimal
from math import pi
false_pi = Decimal('3.141592653589800')
mostly_true_pi = Decimal(pi)
print(f"⇒⇒ {mostly_true_pi}")
print(f"⇒⇒ Error = {((false_pi*10000)-(mostly_true_pi*10000)) * 10**12 } nano_meters") # m # mm # µm # nm  
####
⇒⇒ 3.141592653589793115997963468544185161590576171875
⇒⇒ Error = 68.84002036531000000000000 nano_meters

CRDF · August 24, 2025, 12:11pm

Of course I know some arithmetics, but here this doesn’t happen…
(?)
24.2.7.2 (X86_64) / LibreOffice Community
Build ID: 420(Build:2)
Ubuntu package version: 4:24.2.7-0ubuntu0.24.04.4
Screenshot from 2025-08-24 09-01-56

ajlittoz · August 24, 2025, 12:22pm

Are you sure? Which is the cell format? What happens if you set it to 0.0000000000E+00? Reminder: this does not change the value, just how it is displayed.