Bill Martin -- (Remove NOSPAM from address)
Guest
Re: Error in Addition and Subtraction
stat721 wrote:
> 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
Bookmarks