Most computer hardware and software does math in binary, not decimal. In
binary, most terminating decimal fractions are non-terminating binary
fractions that can only be approximated in binary. When you have to
approximate the inputs, it should be no surprise if the output is also only
approximate.
Almost all binary computer math (including Excel) follows the IEEE standard
for double precision, which gives decimal accuracy of about 15 significant
figures. As a result, decimal digits beyond the 15th usually not what you
would expect. Excel will not display more than 15 decimal digits (it will
pad the display with meaningless zeros if you ask for more), but you can
often get a peak behind the curtain when you cancel leading significant
figures by subtraction.
The two calculations you described use binary values that have the following
decimal equivalents
1.145000000000000017763568394002504646778106689453125
-1.100000000000000088817841970012523233890533447265625
-----------------------------------------------------
0.0449999999999999289457264239899814128875732421875
0.1449999999999999900079927783735911361873149871826171875
-0.1000000000000000055511151231257827021181583404541015625
---------------------------------------------------------
0.044999999999999984456877655247808434069156646728515625
If you do the math, you will see that it is exactly correct, given the
unavoidable initial approximatisons.
If you note where the 15th significant figure is on each of the two answers,
you will see why they display differently.
Jerry
"JohnnyB" wrote:
> One question though, why does the one have all those 9's when you increase
> the decimal place, and the other doesnt? There were no 9's in the original
> numbers, so that is a little strange. Sorry I am bad at math as well....
>
> "JohnnyB" wrote:
>
> > Thank you so much for explaining it, this has answered my question!
> >
> >
> >
> > "Gary''s Student" wrote:
> >
> > > Sadly, the issue IS rounding:
> > >
> > > in A1 enter:
> > > 1.145
> > > in A2 enter:
> > > .145
> > > in B1 enter:
> > > 1.1
> > > In B2 enter:
> > > .1
> > > In C1 enter:
> > > =A1-B1
> > > in C2 enter:
> > > =A2-B2
> > >
> > > The format col C with lots of decimal places. We see:
> > >
> > > 1.145 1.1 0.044999999999999900000000000000
> > > 0.145 0.1 0.045000000000000000000000000000
> > >
> > > That's why, with limited decimal display, C1 and C2 look different.
> > > --
> > > Gary''s Student - gsnu200860
> > >
> > >
> > > "JohnnyB" wrote:
> > >
> > > > Ok here is the issue, it's very easy to re-create in fact.
> > > >
> > > > On a new spreadsheet put 1.145 and subtract 1.1 from it. it should come out
> > > > to .045 right? Then in a new column take .145 and subtract .1 from it. It
> > > > also comes out to .045. Now here is the bug/issue. Change the cell format to
> > > > number, 2 decimal places. The first column changes to .04, the 2nd changes to
> > > > .05. Why is this? And it's not floating point errors, or set precision as
> > > > displayed issues. We are using excel 2007 w/ SP1.
> > > >
> > > > I just want to know why excel changes one answer to .04 when in two decimal
> > > > places, and the other to .05, when they are the exact same in 3 decimal
> > > > places cell format. Again, please don't direct me to floating point KB
> > > > articles, this is not the issue. Just open a new spreadsheet and try what I
> > > > said for yourself to see waht I mean. Thanks for any help!
> > > >
> > > > "Gary''s Student" wrote:
> > > >
> > > > > Post it right here.
> > > > >
> > > > > If it is a know problem, the user community might help you with a workaround.
> > > > > --
> > > > > Gary''s Student - gsnu200860
> > > > >
> > > > >
> > > > > "JohnnyB" wrote:
> > > > >
> > > > > > We have found what I would call a "bug" in the excel 2007 application. I need
> > > > > > to know how I can go about submitting this info to microsoft. Thanks!
|
Similar Threads
