Rounding Error

  • Thread starter Thread starter Ellis Yu
  • Start date Start date
E

Ellis Yu

Dear All,

I found there's a problem in the rounding in formula "sum". I've a
cell to sum all the below figures.

$2,220,459.605
($55,511.49)
($55,511.49)
($1,998,413.65)
($37,182.70)
$4,842.80
($121.07)
($121.07)

The result is $78,440.935 and then I decrease my decimal place to 2. It
becomes $78,440.93 But if I enter the figure directly instead of using
formula "sum" and do it the same way. It becomes $78,440.94. It's so strange
to me. Anyone know how to solve it? Please help

Best Rdgs
Ellis
 
I found there's a problem in the rounding in formula "sum". I've a
cell to sum all the below figures.

$2,220,459.605
($55,511.49)
($55,511.49)
($1,998,413.65)
($37,182.70)
$4,842.80
($121.07)
($121.07)

The result is $78,440.935 and then I decrease my decimal place to 2. It
becomes $78,440.93 But if I enter the figure directly instead of using
formula "sum" and do it the same way. It becomes $78,440.94. It's so strange
to me. Anyone know how to solve it? Please help
Click to expand...

Well, you've seen a little Excel precision problem. The amount you get
by using the "sum" formula isn't exactly 78,440.935--it's a little less.
Excel gives that sum as 78,440.934 999 999 600... So, that explains the
rounding issue and brings up a new one. I personally wasn't aware of
this behavior in Excel (but it's nothing new to computers in general
that use floating point numbers).

So, I'll post this little note for your edification and go off and do
some research on floating point or fixed decimal precision in Excel when
doing addition.
 
Well, you've seen a little Excel precision problem. The amount you get
by using the "sum" formula isn't exactly 78,440.935--it's a little less.
Excel gives that sum as 78,440.934 999 999 600... So, that explains the
rounding issue and brings up a new one. I personally wasn't aware of
this behavior in Excel (but it's nothing new to computers in general
that use floating point numbers).

So, I'll post this little note for your edification and go off and do
some research on floating point or fixed decimal precision in Excel when
doing addition.
Click to expand...

To answer my own post--Use help and do a search for "precision". You'll
find that Excel number precision is 15 digits. Someone with this stuff
fresher in their mind than me will have to jump in, but it looks like
you've asked it to accomodate a range a magnitude + fractional part
greater than that.

It's clumsy, I know, but you can see this behavior if you sum the
numbers that are closer in magnitude together as a group, and then add
the numbers that are outside that range. For example, sum all those
negative numbers as well as the small positive number, and then add that
number that's in the range of 2e6.
 
Excel (and almost all other general purpose software) does math in
binary rather than decimal. None of your decimal fractions (.605, .49,
..65, etc.) have exact binary representations, much as 1/3 has no exact
decimal representation. Therefore all your inputs must be approximated.

The approximation used follows the IEEE standard for double precision
(used by almost all software and hardware). Correctly adding these
approximate inputs results in a sum that is slightly less than
$78,440.935 and therefore the result correctly rounds down. If you
chase through all the details, the binary sum should have a value of
2695210003941281/34359738368, which in decimal is
78440.93499999961932189762592315673828125. Per Excel's documented
limits, Excel only displays 15 figures of this result, but as Mike H has
already noted, Excel's displayed result is 78440.9349999996 which
correctly represents the IEEE result to 15 digits.

If you want to avoid approximations to inputs, do integer math. Excel
exactly represents inputs and correctly calculates the sum of
2220459605
-55511490
-55511490
-1998413650
-37182700
4842800
-121070
-121070
to be 78440935, which you can then divide by 1000 and round to 2 digits
to get the expected result.

Barring that, you must recognize that floating point numbers are by IEEE
definition only accurate to 15 decimal digits. Thus your problem is
effectively
2220459.60500000?????
-55511.4900000000???
-55511.4900000000???
-1998413.65000000?????
-37182.7000000000???
4842.80000000000??
-121.070000000000?
-121.070000000000?
----------------------
78440.93500000?????
which is consistent with the returned result of
78440.9349999996

Since you are only adding and subtracting, and no input had more than 3
decimal places, you could round the sum to 3 decimal places to eliminate
the accumulation of binary approximations, and then round that rounded
result to 2 decimal places.

Jerry
 
Excel (and almost all other general purpose software) does math in
binary rather than decimal. None of your decimal fractions (.605, .49,
.65, etc.) have exact binary representations, much as 1/3 has no exact
decimal representation. Therefore all your inputs must be approximated.
Click to expand...
[]
Nice explanation. You and Mr. McGimpsey have done my Excel research for
me :) Strange-when I saw the OP's message the cause was immediately
clear, but I wonder why I've spent maybe years thinking Excel used fixed
decimal when it could. There's no reason for me to have assumed that :)

Thanks to Mr. Ellis, Mr. McGimpsey, and Mr. Lewis for reminding me of
this.
 
Back
Top