Parentheses change value

[ccgeryde]

G'Day,

I've encountered a strange problem.

I have two values that appear identical, in cells C14 and D14. The
value is 2.44040471836342E-06.

If I enter "=C14-D14" into E14, the returned value is 0.

I I enter "=(C14-D14)" into F14, the returned value is
-1.6940658945086E-21.

The only difference appears to be the parentheses.

The difference is very small at this stage but it's part of a series
of recursive calculations and the error starts to propogate.

Any feedback or suggestions welcome.

Cheers,

Clive Saunders

 

[Niek Otten]

Hi Clive,

Microsoft explains here:

http://support.microsoft.com/kb/78113

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| G'Day,
|
| I've encountered a strange problem.
|
| I have two values that appear identical, in cells C14 and D14. The
| value is 2.44040471836342E-06.
|
| If I enter "=C14-D14" into E14, the returned value is 0.
|
| I I enter "=(C14-D14)" into F14, the returned value is
| -1.6940658945086E-21.
|
| The only difference appears to be the parentheses.
|
| The difference is very small at this stage but it's part of a series
| of recursive calculations and the error starts to propogate.
|
| Any feedback or suggestions welcome.
|
| Cheers,
|
| Clive Saunders
|

 

[joeu2004]

If I enter "=C14-D14" into E14, the returned value is 0.

I I enter "=(C14-D14)" into F14, the returned value is
-1.6940658945086E-21.

The only difference appears to be the parentheses.

Your observation is correct.

To explain: without the parentheses, Excel performs some heuristics
if the last operation is subtraction (or addition?). If the
difference is "close to zero", Excel makes the result zero. This is
explained in an KB somewhere. Sigh, I cannot find my pointer to it at
the moment.

Putting parentheses around the expression avoids the heuristic because
subtraction is no longer the last operation (in Excel's way of looking
at it).

The more significant thing is probably: what is the source of the
small differences, and how do you avoid them?

The small differences arise because of the way binary computers do
arithmetic.

Generally, you can avoid them by judicial use of ROUND().

But be forewarned: that is not a panacea.

 

[ccgeryde]

Thanks to both Nick and Joeu2004 for their replies.

As Joue2004 said, what is the source of the small differences,
especially as they arise from two separate spreadsheets trying to
produce the same results.

That may sound silly but one is intended for daily use as a
"production" spreadsheet, the other is to check the 231 correlations
have been organised correctly in a manul, but much easier to check,
manner.

I'll have to think carefully about this.

Cheers anyway,

Clive

 

[Niek Otten]

Hi Clive,

< two separate spreadsheets trying to produce the same results>

What I used to do in a similar situations (testing of mainframe applications) is explicitly round all intermediate results to an
agreed number of digits and not leave it to hardware/software combinations to determine the rounding.
For instance, in many financial applications it is perfectly acceptable and often necessary to round all intermediate results to
cents.
Otherwise, manual checking would become almost impossible.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Thanks to both Nick and Joeu2004 for their replies.
|
| As Joue2004 said, what is the source of the small differences,
| especially as they arise from two separate spreadsheets trying to
| produce the same results.
|
| That may sound silly but one is intended for daily use as a
| "production" spreadsheet, the other is to check the 231 correlations
| have been organised correctly in a manul, but much easier to check,
| manner.
|
| I'll have to think carefully about this.
|
| Cheers anyway,
|
| Clive