Obvious math error in Excel

  • Thread starter Thread starter superguy
  • Start date Start date
S

superguy

I put in cell A1: 901,253.44
I put in cell A2: 907,122.87
I put in cell A3: =A2-A1

Result = 5,869.43000000005

What gives?
 
What gives ??? Imagine that your 5,869 are dollars and this unexpected error offset is a
coin, a unit. You get paid this coin each and every minute for just existing, posting news
and otherwise doing nothing, 24/7, 365 days a year. It wouldn't take you more than 400
years to earn a cent that way.

I recognise the precision problem if you are hardcoding missile guiding software within
Excel. If not, focus your energy on something meaningful for F&¤, get a life or similar.
 
Limitation of representing (here) decimal representations with a
finite-precision binary system.

Good site for you to check out on this (and many other things) is Chip
Pearson's (cpearson.com). Another thing to be aware of is that this
question is asked very often, and has been answered most every time; so
you can do a Google search of the excel newsgroups
http://groups.google.com/groups?group=microsoft.public.excel
to see the gamut of posted wisdom.

HTH
Dave Braden
 
Hi Superguy,

The reason this is happening is because the numbers are converted from
decimal (base 10) to binary (base 2) so that the microprocessor can
perform the arithmetic, then the result is converted back from binary
to decimal for display in Excel. Since many numbers that can be
represented exactly in decimal can only be represented approximately
in binary (and vice versa), sometimes calculations are slightly
incorrect from our decimal perspective.

Rounding is often helpful but not always bulletproof. The only
bulletproof way to avoid the problem is to never convert the numbers
to binary in the first place, and instead perform the calculations in
decimal. Excel can't do that on it's own, but my Excel Add-In called
xlPrecision can.

You can download the free edition of xlPrecision from:

http://PrecisionCalc.com.


Since xlPrecision does not use the microprocessor to perform its
arithmetic, it's slower than Excel's calculations. But it's fast
enough that you might not notice the difference.

BTW, avoiding the conversion to binary isn't xlPrecision's main
feature. xlPrecision also gives you much higher precision than Excel
alone, and lets you work with much larger, and much smaller, numbers
than Excel can work with alone. Details are at:

http://www.precisioncalc.com/What is xlPrecision.html



Hope this helps,


Greg Lovern
http://PrecisionCalc.com
Get Your Numbers Right
 
Back
Top