Error in Addition and Subtraction

[Guest]

Whomever can help,

I have created a spreadsheet that links balances from day to day. All of
these balances are used in formulas. The problem I am having is when one
specific number is subtracted from another number I am getting an incorrect
answer. For example, I am taking (these are not the actual numbers)
200,000(hardcoded) - 200,000 (referenced to another sheet) and getting
-.0000000023283064365387. It did this on one day when all of the other days
are right. Note: all current day balances are hard coded, only two numbers
are referenced to the day before, and NONE of the numbers have more than two
numbers behind the decimal. So how is this answer possible? One thing i
have done is hard-code the referenced number and I got the right answer. So
I am thinking it has to do with the referenced number, but none of the
referenced numbers have more than two numbers behind the decimal. Please
help! Thanks.

-J

 

[Guest]

Whomever can help,

I have created a spreadsheet that links balances from day to day. All of
these balances are used in formulas. The problem I am having is when one
specific number is subtracted from another number I am getting an incorrect
answer. For example, I am taking (these are not the actual numbers)
200,000(hardcoded) - 200,000 (referenced to another sheet) and getting
-.0000000023283064365387. It did this on one day when all of the other days
are right. Note: all current day balances are hard coded, only two numbers
are referenced to the day before, and NONE of the numbers have more than two
numbers behind the decimal. So how is this answer possible? One thing i
have done is hard-code the referenced number and I got the right answer. So
I am thinking it has to do with the referenced number, but none of the
referenced numbers have more than two numbers behind the decimal. Please
help! Thanks.

-J

--------------

That is how computers generally work. They only carry so many significant
digits. In the case of Excel I believe it's 14 digits which comes from and IEEE
standard. It's the same standard that most software works to.

The problem arises because computers work in binary while people work in
decimal. The conversion between the two systems is not exact for fractions.
For example in decimal there is no exact answer for the division 1/3 -- it
produces an infinite number of repeating digits. Likewise if you convert 0.1
decimal to binary, you end up with an infinitely repeating number so truncating
that binary number and then converting it back to decimal produces a tiny error.

In applications where this is a problem special software can be written to carry
calculations coded in decimal but it greatly slows down the system and is not
often done -- not in any spreadsheet that I'm aware of.

Good luck...

Bill

 

[Guest]

Stat721, even though you may see on two digits to the right of the decimal,
that's just formatting. The underlying data can have 15 digits precision.
Try using the ROUND function in your calculations. HTH

Sincerely, Michael Colvin