Excel Addition Error

[dwright]

Open an Excel spreadsheet, format a column as numeric, no decimals, and paste
in the following 8 values:

442,528
1,477,789
17,942
130,714
84,053
524,606
1,724,962
93,586

Sum them. You will get the answer 4,496,179. The correct answer is
4,496,180. I would suggest you incorporate round-off error correction
routines in your math calculations. This is strictly a newbie error, and I'm
frankly amazed that you haven't caught it yet.


----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

[daddylonglegs]

I think you may be mistaken. There are some circumstances in which Excel's floating point arithmetic can produce erroneous results, but not here, I think. I get the correct answer as I expected - 4496180

[paul]

i get 4496180
i suspect you have a rounding error from somewhere else
--
paul
[email protected]
remove nospam for email addy!



"dwright" wrote:

> Open an Excel spreadsheet, format a column as numeric, no decimals, and paste
> in the following 8 values:
>
> 442,528
> 1,477,789
> 17,942
> 130,714
> 84,053
> 524,606
> 1,724,962
> 93,586
>
> Sum them. You will get the answer 4,496,179. The correct answer is
> 4,496,180. I would suggest you incorporate round-off error correction
> routines in your math calculations. This is strictly a newbie error, and I'm
> frankly amazed that you haven't caught it yet.
>
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...lic.excel.misc

[dwright]

Hey, I'm not kidding. I'm using office 2003, and I double, triple, and
quadruple-checked everything. I initially assumed that Excel was correct,
and the error was mine, or SQL Server's. I have actual witnesses who not
only saw me do the test, but did it themselves and came up with the same
result. I wound up doing the calculation three times by hand to make sure I
wasn't seeing things.

If it is not replicating, then perhaps it is processor-dependent, or it has
been fixed in a recent patch, if so, then that's fine. I can send the
offending spreadsheet if you like.

[Jerry W. Lewis]

I have never seen an instance where Excel's basic arithmetic was not correct
per the IEEE 754 standard. Moreover I have always found integer arithmetic
exactly match naive expectations. I have been unable to reproduce your
results despite attempts in 4 Office versions (2000, 2002, 2003, & 2007) on 3
PCs.

The most common problem when people think they see an Excel math error is
that their inputs are calculated results that are formatted to hide decimal
places that do enter into the calculation in question. Did you follow your
own directions (copy/paste from your newsgroup post into Excel and sum those
values)?

The next most common problem, is that Tools|Options|Calculation is set to
manual recalculation, so that calculations are not updated when inputs are
edited.

Another problem that can occur with multivariate functions (such as SUM())
is that text that only looks like numbers will be ignored, but I see no
opportunity in your example for that to be an issue.

If you still get your reported result after ruling out the preceding causes,
is it reproducible in other packages? My guess is that Excel's basic
arithmetic is performed in the processor chip instead of in software.
Similarly, I doubt that other packages would re-invent the wheel, so I would
expect a processor math bug to be manifested in other calculational software.
If it is reproducible in other packages, you should contact the manufacturer
of your computer to report a defective chip.

If the problem is not attributable to any of these sources, then you should
post back with the exact cells/formulas involved as well as the exact version
of Excel, the operating system, and hardware information.

Jerry

"dwright" wrote:

> Hey, I'm not kidding. I'm using office 2003, and I double, triple, and
> quadruple-checked everything. I initially assumed that Excel was correct,
> and the error was mine, or SQL Server's. I have actual witnesses who not
> only saw me do the test, but did it themselves and came up with the same
> result. I wound up doing the calculation three times by hand to make sure I
> wasn't seeing things.
>
> If it is not replicating, then perhaps it is processor-dependent, or it has
> been fixed in a recent patch, if so, then that's fine. I can send the
> offending spreadsheet if you like.

[Dave Peterson]

I also get 4,496,180.

How about formatting those cells to sum as General and widening the column.
Maybe some of the decimal portions are hidden (by format or by column width).



dwright wrote:
>
> Open an Excel spreadsheet, format a column as numeric, no decimals, and paste
> in the following 8 values:
>
> 442,528
> 1,477,789
> 17,942
> 130,714
> 84,053
> 524,606
> 1,724,962
> 93,586
>
> Sum them. You will get the answer 4,496,179. The correct answer is
> 4,496,180. I would suggest you incorporate round-off error correction
> routines in your math calculations. This is strictly a newbie error, and I'm
> frankly amazed that you haven't caught it yet.
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...lic.excel.misc

--

Dave Peterson

[David F. Cox]

Perhaps the "somewhere else" may have non-integers formatted as integer?

is it just paste, or paste special?


"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
>I also get 4,496,180.
>
> How about formatting those cells to sum as General and widening the
> column.
> Maybe some of the decimal portions are hidden (by format or by column
> width).
>
>
>
> dwright wrote:
>>
>> Open an Excel spreadsheet, format a column as numeric, no decimals, and
>> paste
>> in the following 8 values:
>>
>> 442,528
>> 1,477,789
>> 17,942
>> 130,714
>> 84,053
>> 524,606
>> 1,724,962
>> 93,586
>>
>> Sum them. You will get the answer 4,496,179. The correct answer is
>> 4,496,180. I would suggest you incorporate round-off error correction
>> routines in your math calculations. This is strictly a newbie error, and
>> I'm
>> frankly amazed that you haven't caught it yet.
>>
>> ----------------
>> This post is a suggestion for Microsoft, and Microsoft responds to the
>> suggestions with the most votes. To vote for this suggestion, click the
>> "I
>> Agree" button in the message pane. If you do not see the button, follow
>> this
>> link to open the suggestion in the Microsoft Web-based Newsreader and
>> then
>> click "I Agree" in the message pane.
>>
>> http://www.microsoft.com/office/comm...lic.excel.misc
>
> --
>
> Dave Peterson

[dwright]

"Jerry W. Lewis" wrote:

> I have never seen an instance where Excel's basic arithmetic was not correct
> per the IEEE 754 standard. Moreover I have always found integer arithmetic
> exactly match naive expectations. I have been unable to reproduce your
> results despite attempts in 4 Office versions (2000, 2002, 2003, & 2007) on 3
> PCs.

I never doubted Excel math routines myself. Until now.

> The most common problem when people think they see an Excel math error is
> that their inputs are calculated results that are formatted to hide decimal
> places that do enter into the calculation in question. Did you follow your
> own directions (copy/paste from your newsgroup post into Excel and sum those
> values)?

Ghaaa. Do I have 'Idiot" tattooed on my forehead? Of course they're not
calculated results, or based on results that have come from a calculation, or
based on results that at any time in the past have not been integers. Thus
far myself and another person have replicated the experiment on two different
processors: An Intel P4 2.8 GHz Hyperthreaded, and an Athlon 64 X2.

Yes I did follow my own directions. The figures supplied were from a new
spreadsheet created by pasting those 8 values into it to make sure it was
real, and double-checking the result manually.

> The next most common problem, is that Tools|Options|Calculation is set to
> manual recalculation, so that calculations are not updated when inputs are
> edited.

Highlight column of numbers + 1 row. Click on Sum button. Sum appears. No
editing involved.

> Another problem that can occur with multivariate functions (such as SUM())
> is that text that only looks like numbers will be ignored, but I see no
> opportunity in your example for that to be an issue.

Quite correct.

> If you still get your reported result after ruling out the preceding causes,
> is it reproducible in other packages? My guess is that Excel's basic
> arithmetic is performed in the processor chip instead of in software.
> Similarly, I doubt that other packages would re-invent the wheel, so I would
> expect a processor math bug to be manifested in other calculational software.
> If it is reproducible in other packages, you should contact the manufacturer
> of your computer to report a defective chip.

The reason I found this result in the first place is because the result from
Excel and SQL Server were different. It turns out that SQL Server was
correct, but it contains the concept of an integer, so it can have no
round-off error in a case like this. Excel has no concept of an integer, so
it can have a round-off error. Hence, this is not a processor math bug, or
else SQL Server would have given me the same result as Excel. It did not.
It gave me the correct result.

> If the problem is not attributable to any of these sources, then you should
> post back with the exact cells/formulas involved as well as the exact version
> of Excel, the operating system, and hardware information.

You have the exact cells. Verbatim. Cut and pasted from Excel directly
into this forum. The only formula is the one advised: Sum the columns, so
the only formula involved is the one you create yourself. Operating system
in all cases is Windows XP Professional. Hardware is:

1) Pentium 4 2.8 GHz hyperthreaded.
2) Athlon 64 x2 6800+ Dual core.

Just a quick check. You guys are actually conducting the experiment,
correct? You're not just dusting me off with facts untried and untested
because you don't believe me, right?

It wouldn't be the first time that happened. Just blow off the experiment
because you don't believe it, but claim that you actually performed the
experiment for the sake of credibility. 'Fess up, people. You did actually
try this, right? Be honest.

[dwright]

Yikes! My bad. I went back to the original data to confirm my statement
that nothing had ever been anything but an integer, and I was WRONG. The
actual original inputs were:

442527.6
1477789.2
17942.4
130713.6
84052.8
524606.4
1724961.6
93585.6

Hence, my premise was wrong. I apologise to the community for my error.

"dwright" wrote:

> Open an Excel spreadsheet, format a column as numeric, no decimals, and paste
> in the following 8 values:
>
> 442,528
> 1,477,789
> 17,942
> 130,714
> 84,053
> 524,606
> 1,724,962
> 93,586
>
> Sum them. You will get the answer 4,496,179. The correct answer is
> 4,496,180. I would suggest you incorporate round-off error correction
> routines in your math calculations. This is strictly a newbie error, and I'm
> frankly amazed that you haven't caught it yet.
>
>
> ----------------
> This post is a suggestion for Microsoft, and Microsoft responds to the
> suggestions with the most votes. To vote for this suggestion, click the "I
> Agree" button in the message pane. If you do not see the button, follow this
> link to open the suggestion in the Microsoft Web-based Newsreader and then
> click "I Agree" in the message pane.
>
> http://www.microsoft.com/office/comm...lic.excel.misc

[Ron Rosenfeld]

On Fri, 14 Jul 2006 16:46:01 -0700, dwright <[email protected]>
wrote:

>Open an Excel spreadsheet, format a column as numeric, no decimals, and paste
>in the following 8 values:
>
> 442,528
> 1,477,789
> 17,942
> 130,714
> 84,053
> 524,606
> 1,724,962
> 93,586
>
>Sum them. You will get the answer 4,496,179. The correct answer is
>4,496,180. I would suggest you incorporate round-off error correction
>routines in your math calculations. This is strictly a newbie error, and I'm
>frankly amazed that you haven't caught it yet.
>
>
>----------------
>This post is a suggestion for Microsoft, and Microsoft responds to the
>suggestions with the most votes. To vote for this suggestion, click the "I
>Agree" button in the message pane. If you do not see the button, follow this
>link to open the suggestion in the Microsoft Web-based Newsreader and then
>click "I Agree" in the message pane.
>
>http://www.microsoft.com/office/comm...lic.excel.misc


Go back to your worksheet which shows this "error".

Select the cells and reformat them as number with 15 decimals.

What do you see?
--ron

[Jerry W. Lewis]

Apology accepted. For future reference, it is generally best not to accuse
people of dusting you off, when instead you are the one dusting them off.
Several poster's (including myself) suggested tests that would have revealed
this had you tried their suggestions.

Jerry

"dwright" wrote:

> Yikes! My bad. I went back to the original data to confirm my statement
> that nothing had ever been anything but an integer, and I was WRONG. The
> actual original inputs were:
>
> 442527.6
> 1477789.2
> 17942.4
> 130713.6
> 84052.8
> 524606.4
> 1724961.6
> 93585.6
>
> Hence, my premise was wrong. I apologise to the community for my error.