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!

Hey there! I understand how frustrating it can be when Excel doesn't add up numbers correctly. The reason for this discrepancy could be due to the way Excel handles decimal places. Here are a few things you can try to fix this issue:

1. Check the number formatting: Make sure that the cells containing the dollar amounts are formatted as currency with two decimal places. To do this, select the cells and go to Format Cells Number Currency.
2. Round the numbers: If the numbers you are adding up have more than two decimal places, Excel may be rounding them differently depending on the method you use to add them up. Try rounding the numbers to two decimal places before adding them up. You can do this by using the ROUND function, like this:
   Formula:

   =SUM(ROUND(A1:A5,2)) 

3. Use the SUM function with a tolerance: You can use the SUM function with a tolerance to allow for small rounding errors. For example,
   Formula:

   =SUM(A1:A5,0.001) 

   will add up the numbers in cells A1 to A5, but allow for a tolerance of 0.001.

I hope these tips help you fix the issue and prevent any future headaches!

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?

--
Biff
Microsoft Excel MVP


"Access Joe" wrote in message
...
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!

"Access Joe" wrote:
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" wrote:
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!

Thanks guys. I appreciate your help.

"Joe User" wrote:

"Access Joe" wrote:
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" wrote:
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!

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

"Access Joe" wrote in message
...
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!

On 19 Feb., 17:48, "Fred Smith" wrote:
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