how does arithmetic exactly work in Calc?

[Michel Onoff]

Hi Eike


On 2020-12-03 17:41, Eike Rathke wrote:
> Hi Michel,
>
> On Thursday, 2020-12-03 13:48:25 +0100, Michel Onoff wrote:
>
>> I would like to understand how simple arithmetic (+ - * /) works in Calc.
>> I kind of guess that the underlying internal number representation is
>> IEEE 754 double (64 bit).
>
> Yes.
>
>> I also guess that when a double is shown on the sheet it is approximated
>> by a decimal with at most 15 significand digits.
>
> Yes.
>
>> To reproduce the behavior below, use scientific notation with 20 digits
>> or more.
>
> Note that with increasing digits beyond 15 significands you currently
> will not gain anything except additional zeros

Sure, but I just want to make sure that the binary->decimal conversion
works as advertised. Setting 20 digits helps with my peace of mind about
that.



>
>> I have two slightly different number x and x'.
>> x is 2^-49 (a formula) while x' is 1.77635683940025E-15 (a literal).
>> Their decimal representation appear equal on the sheet, but they are,
>> indeed, slightly different internally. You can set them apart as follows:
>>
>> y = x - 1.7763568394002E-15
>> y' = x' - 1.7763568394002E-15
>>
>> that is, by subtracting the same number from x and x'.
>> y and y' appear differently on the sheet, meaning that x and x' are
>> different internally to start with.
>
> Yes.
>
>> However, x - x' is exactly 0. In IEEE 754 arithmetic, two numbers are
>> equal if and only if their difference is 0. That would mean that x and
>> x' are equal, which they are not from the above.
>
> The Calc + and - operators try to cater for numeric inaccuracies and tie
> to zero in some ranges, otherwise the famous example of 0.3 - 0.2 - 0.1
> would not be 0.0 but -2.77555756156289E-17 instead.
>

This strategy simply postpones the issue to a later point. By returning
to my example, x - x' returns 0 but y - y' does not.

It's harder to reason when there are exceptional rules in place, like
this one for nearby values, that depend on the data at hand. What's
worse, there seems to be no real spec, beside the implementation.




(The real culprit, of course, is trying to emulate decimal arithmetic
with binary arithmetic and adding exceptional rules to please humans and
overcome some seemingly strange behavior. While IEEE binary arithmetic
is unfamiliar to most users, at least it is very regular.
But I'm afraid it's far too late, and vastly incompatible, to introduce
pure decimal arithmetic in Calc (or Excel, for that matter))





> If you want the "raw" subtraction of your two values you can check with
> =RAWSUBTRACT(2^-49;1.77635683940025E-015)
> that yields 3.94430452610506E-31
>

Didn't know about RAWSUBTRACT(), nice to have!



> The double to string conversion needs further improvement to be able to
> more precisely represent values in decimals so could actually convert
> 2^-49 to 1.7763568394002505E-15
>

That would be more than the canonical 15 digits, though... You would
sometimes have to output up to 17 digits to distinguish different IEEE
doubles.