Calc sometimes off by a penny

[Access Joe]

Hey everyone. Excel 2003.

I will frequently add up a range of cells (all dollar amounts that were
typed in manually). When I add those numbers up with the calculator, let's
say they equal $1865.60. Then when I take them and put the same exact
numbers into a Column and do an autosum, the number comes up $1865.60. GREAT!

But when I do an =SUM(... and then highlight the cells containing the same
numbers, the total come up $1865.59 (one penny off). Of course, it's not all
the time. But I've noticed a difference sometimes between AutoSum and
manually entering a forumla (usually by just a penny). Why is this happening
and how can I fix this before I loose my mind? Any help would be
appreciated. THANKS!

 

[T. Valko]

all dollar amounts that were typed in manually

do an autosum

By "autosum", do you mean you click the AutoSum icon on the standard
toolbar?

If so, that's really strange since the AutoSum icon inserts a SUM function
into the cell. It would be the same as the formula you would type.

A1 = 5
A2 = 5
A3 = 7
A4 = 3
A5 = 7
A6

Select the range A1:A6
Click the AutoSum icon

A6 now contains the formula =SUM(A1:A5)

It would be no different from you actually typing the formula into A6
manually.

What version of Excel are you using?

Can you post an example of the numbers where the two methods produce
different results?

 

[Joe User]

Why is this happening and how can I fix this
before I loose my mind?

I cannot explain the difference between "autosum" and SUM.

But "penny off" errors (more and less) are quite common when using numbers
with decimal fractions. Most such numbers cannot be represented exactly
internally. That leads to all kinds of anomalies. Here's a fun one to try:
=IF(10.1-10=0.1,TRUE). It returns FALSE(!).

Nothing really wrong. It's just an issue with binary computer arithmetic
that we all need to deal with. Two common solutions:

1. Use ROUND prolifically, but prudently. Use if for most calculations with
dollar-and-cents results. Do not use for some intermediate computations,
notably when computing periodic interest rates and when using them to compute
interest, principal and balance in an amortization schedule, for example.

Note: There are good reasons to use ROUND even with such simply
arithmetic operations as =A1-A2. As demonstrated by the 10.1-0.1 example
above, sometimes this can lead to infinitesimal differences that cause
comparision for equality to fail.

2. Set the "Precision as displayed" option (PAD) under Tools > Options >
Calculation.

I usually deprecate #2 because it is not selective. If you choose to try
it, be sure to make a copy of the Excel file first. Once you set PAD, some
constants might be changed irreversibly. If you decide that was a mistake,
you would have to re-enter the constant. Or return to your back-up copy. ;-)


----- original message -----

 

[Access Joe]

Thanks guys. I appreciate your help.

I cannot explain the difference between "autosum" and SUM.

But "penny off" errors (more and less) are quite common when using numbers
with decimal fractions. Most such numbers cannot be represented exactly
internally. That leads to all kinds of anomalies. Here's a fun one to try:
=IF(10.1-10=0.1,TRUE). It returns FALSE(!).

Nothing really wrong. It's just an issue with binary computer arithmetic
that we all need to deal with. Two common solutions:

1. Use ROUND prolifically, but prudently. Use if for most calculations with
dollar-and-cents results. Do not use for some intermediate computations,
notably when computing periodic interest rates and when using them to compute
interest, principal and balance in an amortization schedule, for example.

Note: There are good reasons to use ROUND even with such simply
arithmetic operations as =A1-A2. As demonstrated by the 10.1-0.1 example
above, sometimes this can lead to infinitesimal differences that cause
comparision for equality to fail.

2. Set the "Precision as displayed" option (PAD) under Tools > Options >
Calculation.

I usually deprecate #2 because it is not selective. If you choose to try
it, be sure to make a copy of the Excel file first. Once you set PAD, some
constants might be changed irreversibly. If you decide that was a mistake,
you would have to re-enter the constant. Or return to your back-up copy. ;-)


----- original message -----

 

[Fred Smith]

It's rounding. When you see 67.42 in a cell, the actual value may be 67.416.
Excel rounds this to 67.42 because you asked for 2 decimal places to
display, but still uses 67.416 in the Sum calculation. When you have more
than one cell like this, your results are off by a penny.

To fix the error, put =round(...,2) around your formulas.

Regards,
Fred

 

[Bernd P]

It's rounding. When you see 67.42 in a cell, the actual value may be 67.416.
Excel rounds this to 67.42 because you asked for 2 decimal places to
display, but still uses 67.416 in the Sum calculation. When you have more
than one cell like this, your results are off by a penny.

To fix the error, put =round(...,2) around your formulas.
...

Round(sum()) can still be different from sum(round()). If you need to
"fix" this:
http://www.sulprobil.com/html/largest_remainder.html

Regards,
Bernd