Prev: combine/merge 2 worksheet (tabs) into 1 work sheet
Next: Sharing Macros
From: Access Joe on 18 Feb 2010 21:57
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!

From: T. Valko on 18 Feb 2010 22:28
>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" <AccessJoe(a)discussions.microsoft.com> wrote in message
news:EF10E7F7-F22D-413D-BBF8-A632C2D3162A(a)microsoft.com...
> 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!
>


From: Joe User on 18 Feb 2010 23:15
"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!
>
From: Access Joe on 19 Feb 2010 09:02
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!
> >
From: Fred Smith on 19 Feb 2010 11:48
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" <AccessJoe(a)discussions.microsoft.com> wrote in message
news:EF10E7F7-F22D-413D-BBF8-A632C2D3162A(a)microsoft.com...
> 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!
>

 |  Next  |  Last
Pages: 1 2
Prev: combine/merge 2 worksheet (tabs) into 1 work sheet
Next: Sharing Macros