Parentheses change value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ccgeryde@yahoo.com.au - 09 Apr 2008 05:46 GMT

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

Reply to this Message

Niek Otten - 09 Apr 2008 07:52 GMT

Hi Clive,

Microsoft explains here:

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

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| G'Day,
|
[quoted text clipped - 18 lines]
|
| Clive Saunders

Reply to this Message

joeu2004 - 09 Apr 2008 07:57 GMT

On Apr 8, 8:46 pm, ccger...@yahoo.com.au wrote:

> 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.

Reply to this Message

ccgeryde@yahoo.com.au - 09 Apr 2008 13:44 GMT

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

Reply to this Message

Niek Otten - 09 Apr 2008 14:43 GMT

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.

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| Thanks to both Nick and Joeu2004 for their replies.
|
[quoted text clipped - 12 lines]
|
| Clive

Reply to this Message