Prev: Sumproduct - Return value as Blank
Next: cross-hares
From: chitown29 on 25 May 2010 18:21
In Excel 2003 and 2007, why, when adding 664,199.05 and negative 582,911.61
the answer comes out 81,287.4400000001? Why is there a 1 at the end? This
causes issues when building formulas to control two sets of information that
should equal one another.
From: Fred Smith on 25 May 2010 18:28
That's the way computers work. They work in binary, we work in decimal.
There are imprecisions in the translation process, as you've found out. If
you want a specific precision, use the Round function, as in:
=round(yourformula,2)

Regards,
Fred


"chitown29" <chitown29(a)discussions.microsoft.com> wrote in message
news:1EB5596A-B8A8-4A9D-BF79-6D9CEB62B4E6(a)microsoft.com...
> In Excel 2003 and 2007, why, when adding 664,199.05 and negative
> 582,911.61
> the answer comes out 81,287.4400000001? Why is there a 1 at the end?
> This
> causes issues when building formulas to control two sets of information
> that
> should equal one another.

From: Bernard Liengme on 27 May 2010 13:05
Never test if two values are equal with formulas like
=A1=B1
But use
=ROUND(A1-B1,12)=0
or
ABS(A1-B1)<1e-12

This will get around the IEEE rounding errors

If you want to delve deeper:

Chip's clear explanation
http://www.cpearson.com/excel/rounding.htm

Floating-point arithmetic may give inaccurate results in Excel
http://support.microsoft.com/kb/78113/en-us

(Complete) Tutorial to Understand IEEE Floating-Point Errors
http://support.microsoft.com/kb/42980

What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm

Visual Basic and Arithmetic Precision
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q279/7/55.ASP&NoWebContent=1

Good reading from T Valko
http://blogs.msdn.com/excel/archive/2008/04/10/understanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers.aspx

Others:
http://support.microsoft.com/kb/214118

http://docs.sun.com/source/806-3568/ncg_goldberg.html

best wishes
--
Bernard Liengme
Microsoft Excel MVP
people.stfx.ca/bliengme
email address: remove uppercase characters

REMEMBER: Microsoft is closing the newsgroups; We will all meet again at
http://answers.microsoft.com/en-us/office/default.aspx#tab=4

"chitown29" <chitown29(a)discussions.microsoft.com> wrote in message
news:1EB5596A-B8A8-4A9D-BF79-6D9CEB62B4E6(a)microsoft.com...
> In Excel 2003 and 2007, why, when adding 664,199.05 and negative
> 582,911.61
> the answer comes out 81,287.4400000001? Why is there a 1 at the end?
> This
> causes issues when building formulas to control two sets of information
> that
> should equal one another.

From: Gary''s Student on 27 May 2010 16:21
This is a common problem called rounding error. It can be avoided as follows:

=ROUND(664199.05-582911.61,2)

--
Gary''s Student - gsnu201003


"chitown29" wrote:

> In Excel 2003 and 2007, why, when adding 664,199.05 and negative 582,911.61
> the answer comes out 81,287.4400000001? Why is there a 1 at the end? This
> causes issues when building formulas to control two sets of information that
> should equal one another.
 | 
Pages: 1
Prev: Sumproduct - Return value as Blank
Next: cross-hares