Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

[BUG] Undesired change of the calculation formula during sequence calculation

[BUG]
Undesired change of the calculation formula in sequence

[Version]
version 2016, version 97, or maybe all versions
Tested with different documents at different machines.

[Example]
In the following calculations:
R1C1 =-1.000000000000000
R2C1=R[-1]C+0.1 =-0.9000000000000000
R3C1=R[-1]C+0.1 =-0.8000000000000000
R4C1=R[-1]C+0.1 =-0.7000000000000000
R5C1=R[-1]C+0.1 =-0.6000000000000000
R6C1=R[-1]C+0.1 =-0.5000000000000000
R7C1=R[-1]C+0.1 =-0.4000000000000000
R8C1=R[-1]C+0.1 =-0.3000000000000000
R9C1=R[-1]C+0.1 =-0.2000000000000000
R10C1=R[-1]C+0.1 =-0.1000000000000000
R11C1=R[-1]C+0.1 =-1.4E-16 <-- Wrong !
R12C1=R[-1]C+0.1 =0.1000000000000000
...

But: RxCx =-0.1+0.1 =0 <-- correct !!!

(It is known that the calculation error (e.g. -1.4E-16) from the decimal-binary exchange with limited bits will be significant when the result comes to "0". But this is not the point here!)

Here the point is:
- For R11C1=R[-1]C+0.1, the formula "R[-1]C+0.1" should be used, therefore
"R11C1 =R[-1]C+0.1 =R10C1 =-0.1+0.1 =0" is expected.
- But the real calculation seems to be:
"R11C1 =R1C1+0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1 =-1+0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1+0.1 =-1.4E-16" is undesired!
- This means the calculation formula was changed by Excel in this sequence.

Excel should NOT change the user's formula by any reason. This is the point which I claim it is a bug.

1 vote
Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)

We’ll send you updates on this idea

DB shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

1 comment

Sign in
(thinking…)
Sign in with: facebook google
Signed in as (Sign out)
Submitting...
  • DB commented  ·   ·  Flag as inappropriate

    Unless in Excel the real floating-point is not stored as 64 bits but 80 bits. Otherwise how to explain "-0.1+0.1 =0" but "R10C1+0.1 =-1.4E-16" ?

Feedback and Knowledge Base