Calculation is incorrect

[Scott]

I am using Excel 2003 and have run into a bit of a problem.
In cell A1, I enter the following formula:
=725.27-(248.33+69.54+40+111.08+20+29.95+20+33.44+18.58+15+40+20+10.05+25+15.68+7.8)
In cell A2, I enter the number: .82 (which is the result of the formula
above if you add it up manually)
In cell A3, I enter this formula:
=If(A1=A2,"Yes","No")
The result of this last formula should be "Yes" as the 2 cells should be
equal.
However, the result is "No" When I started digging into this, I reformatted
the cells to number with 16 decimal places. and this is what they showed:
Cell A1: 0.8200000000001640
Cell A2: 0.8200000000000000
I know this isn't much of a difference, but it is enough to keep the formula
in A3 from giving a "Yes" result. I know I can just use the ROUND function to
patch over this, but I was just wondering why excel seems to be adding this
string of numbers incorrectly?

[Fred Smith]

Humans calculate in decimal. Computers calculate in binary. The conversion
between the two is often imperfect, as you've found out. This has been the way
computers have worked since their invention, and it's not going to change.

Use Round to solve your problem.

--
Regards,
Fred


"Scott" <[email protected]> wrote in message
news:[email protected]...
>I am using Excel 2003 and have run into a bit of a problem.
> In cell A1, I enter the following formula:
> =725.27-(248.33+69.54+40+111.08+20+29.95+20+33.44+18.58+15+40+20+10.05+25+15.68+7.8)
> In cell A2, I enter the number: .82 (which is the result of the formula
> above if you add it up manually)
> In cell A3, I enter this formula:
> =If(A1=A2,"Yes","No")
> The result of this last formula should be "Yes" as the 2 cells should be
> equal.
> However, the result is "No" When I started digging into this, I reformatted
> the cells to number with 16 decimal places. and this is what they showed:
> Cell A1: 0.8200000000001640
> Cell A2: 0.8200000000000000
> I know this isn't much of a difference, but it is enough to keep the formula
> in A3 from giving a "Yes" result. I know I can just use the ROUND function to
> patch over this, but I was just wondering why excel seems to be adding this
> string of numbers incorrectly?

[JMB]

Problem w/binary fractions.

http://www.cpearson.com/excel/rounding.htm



"Scott" wrote:

> I am using Excel 2003 and have run into a bit of a problem.
> In cell A1, I enter the following formula:
> =725.27-(248.33+69.54+40+111.08+20+29.95+20+33.44+18.58+15+40+20+10.05+25+15.68+7.8)
> In cell A2, I enter the number: .82 (which is the result of the formula
> above if you add it up manually)
> In cell A3, I enter this formula:
> =If(A1=A2,"Yes","No")
> The result of this last formula should be "Yes" as the 2 cells should be
> equal.
> However, the result is "No" When I started digging into this, I reformatted
> the cells to number with 16 decimal places. and this is what they showed:
> Cell A1: 0.8200000000001640
> Cell A2: 0.8200000000000000
> I know this isn't much of a difference, but it is enough to keep the formula
> in A3 from giving a "Yes" result. I know I can just use the ROUND function to
> patch over this, but I was just wondering why excel seems to be adding this
> string of numbers incorrectly?

[Scott]

Thanks for your replies, I did use the round function to solve my problem,
but was just curious as to why it hapened.
Thanks again

"Scott" wrote:

> I am using Excel 2003 and have run into a bit of a problem.
> In cell A1, I enter the following formula:
> =725.27-(248.33+69.54+40+111.08+20+29.95+20+33.44+18.58+15+40+20+10.05+25+15.68+7.8)
> In cell A2, I enter the number: .82 (which is the result of the formula
> above if you add it up manually)
> In cell A3, I enter this formula:
> =If(A1=A2,"Yes","No")
> The result of this last formula should be "Yes" as the 2 cells should be
> equal.
> However, the result is "No" When I started digging into this, I reformatted
> the cells to number with 16 decimal places. and this is what they showed:
> Cell A1: 0.8200000000001640
> Cell A2: 0.8200000000000000
> I know this isn't much of a difference, but it is enough to keep the formula
> in A3 from giving a "Yes" result. I know I can just use the ROUND function to
> patch over this, but I was just wondering why excel seems to be adding this
> string of numbers incorrectly?

[BrianH]

If you're testing for equality of two numbers caluclated via two different
routes, it's often safer to use IF(ABS(A1-A2)<0.001 . . . or some other
suitable small number, depending on the magnitude of the numbers in question.

BrianH

"Scott" wrote:

> Thanks for your replies, I did use the round function to solve my problem,
> but was just curious as to why it hapened.
> Thanks again
>
> "Scott" wrote:
>
> > I am using Excel 2003 and have run into a bit of a problem.
> > In cell A1, I enter the following formula:
> > =725.27-(248.33+69.54+40+111.08+20+29.95+20+33.44+18.58+15+40+20+10.05+25+15.68+7.8)
> > In cell A2, I enter the number: .82 (which is the result of the formula
> > above if you add it up manually)
> > In cell A3, I enter this formula:
> > =If(A1=A2,"Yes","No")
> > The result of this last formula should be "Yes" as the 2 cells should be
> > equal.
> > However, the result is "No" When I started digging into this, I reformatted
> > the cells to number with 16 decimal places. and this is what they showed:
> > Cell A1: 0.8200000000001640
> > Cell A2: 0.8200000000000000
> > I know this isn't much of a difference, but it is enough to keep the formula
> > in A3 from giving a "Yes" result. I know I can just use the ROUND function to
> > patch over this, but I was just wondering why excel seems to be adding this
> > string of numbers incorrectly?