Feedback by UserVoice

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

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

Undesired change of the calculation formula in sequence

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

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
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
Sign in with: Facebook Google
Signed in as (Sign out)
  • 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