EXCEL 2007 CALCULATION ERROR

[Thauzar]

In windows, if you open calculator and multiply 850*77,1=65535

In Excel 2007, if you multiply 850*77,1=62065,5

some people, in a legit Excel 2007 Enterprise edition, get a result of 100000!!!

What's up with that? Can anyone here confirm this? How can we make sure Excel is calculating right?

Btw my version is up-to-date, I can't tell for the enterprise one but still it's both answers are far from the right answer.

[tecknomage]

In windows, if you open calculator and multiply 850*77,1=65535

In Excel 2007, if you multiply 850*77,1=62065,5

some people, in a legit Excel 2007 Enterprise edition, get a result of 100000!!!

What's up with that? Can anyone here confirm this? How can we make sure Excel is calculating right?

Btw my version is up-to-date, I can't tell for the enterprise one but still it's both answers are far from the right answer.

Please clearify your calculation. What do you mean by "1=65535" or "1=62065,5"

For me, in both Calculator & Excel: 850*77 = 65450

[Thauzar]

it reads 77,1 it's in decimal... 77,1

[jaclaz]

Cannot say about Excel 2007, but something similar is common in every release of spreadsheet apps.

It depends on the separator you use, in US and Britain are in use the comma as separator for thousands and the period or full stop to separate decimal numbers.

In Europe generally it is the other way round.

Be aware that there TWO separate settings for this, usually spreadsheet apps use the default "International" settings, but some have also an "internal" setting.

Instead of putting "=850*77,1" in a cell, try the following:

Input in cells A1 and A2 "850" (without quotes)

Input in cell B1 "77.1"

Input in cell B2 "77,1"

Input in cell C1 "=A1*B1"

Input in cell C2 "=A2*B2"

Now copy the six cells a couple of rows below, and set cell formats "Number" to "General", you should see why it happens.

To check results, when in doubt, always enter integers, like:

=850*771/10

This has nothing to do with Excel (or any other spreadsheet for that matter) with any versions: you are simply giving to it data in a way that the app parses as being NOT the number you think you typed.

jaclaz

[cluberti]

Yes, this is a documented issue and a fix is in the works. Nothing more I know about it than that, although if you multiply the results of the initial calculation by 2, you'll get the desired number, so the calculation itself is fine, but the display logic is flawed.

[jaclaz]

I take back my previous post (it is correct but not related to the issue at hand).

It appears to be a real BUG specific to Excel 2007:

http://bink.nu/news/microsoft-confirms-exc...lation-bug.aspx

http://www.accountingweb.co.uk/cgi-bin/ite...1023&f=1026

jaclaz

P.S.:

judging from some of the reports here:

http://blogs.msdn.com/excel/archive/2007/0...sue-update.aspx

# Any calculations based off that cell will be accurate too? Is that so?

Wednesday, September 26, 2007 2:59 AM by cmart02

David,

You said "Any calculations based off that cell will be accurate too. Hope that helps."

But that's not entirely correct. At least not from what I have seen. If you happen to be rounding your calculations (=ROUND(850*77.1,2)*2), it also rounds it to 100K making that permanent.

So, while it is true that most cases Excel treats the value as correct except for the visual side of it, in others it actually DOES treat it as 100K.

Rob

# re: Calculation Issue Update

Thursday, September 27, 2007 11:35 PM by wlandrum

I think that I have found a second related bug that is in both the 2003 version of Excel and the 2007 version. Namely:

If you key in the following:

=DEC2HEX(2^16), it correctly gives 10000 (hex)

=DEC2HEX(2^16-1), it correctly gives FFFF (hex)

=DEC2HEX(65535), it correctly gives FFFF (hex)

=DEC2HEX(850*77.1), it incorrectly gives FFFE (hex)

Unlike the original statement, the "wrong" result can be "propagated" in a sheet, so it is not just a "display" problem, and everyone using Excel for financial calculations in his right mind will use the ROUND() function, as simply put by user Jerome:

# re: Calculation Issue Update

Thursday, September 27, 2007 5:24 AM by Jerome

That's why most of us are staying with Office 2000 and Windows XP.

Remember my boss was so mad when his wireless keyboard missed a key stroke. One hundred thousand became ten thousand could be a very serious mistake.

If you send out an Excel order form to order 850 pieces of $77.1 parts. The recipient open it with Excel 2007 and charge you $100,000. This is no fun.

Edited September 29, 2007 by jaclaz

[Thauzar]

Thanks for all the replies, with links and all, I'll take time to read everything. This seem like a HUGE problem. I just hope they fix this asap because here ppl are making a push to switch all Office suites to OpenOffice and I love the ribbon

Thanks

[jaclaz]

Just to close this topic, a hotfix has been released:

http://support.microsoft.com/kb/943075/en-us

Though not very much publicized.

The basic statement hinting that it is a minor bug as it involves the "showing" of result, as the calculatin is performed correctly, which has been already proven to be false is still there:

INTRODUCTION

Issue that the hotfix package fixes

This hotfix package fixes the following issue that was not previously documented in a Microsoft Knowledge Base article:

• When you perform a calculation in Excel 2007, the following behavior occurs:

• The result of the calculation is a number from 65534.99999999995 to 65535. The calculation is performed correctly. However, the result is incorrectly shown as 100000.

• The result of the calculation is a number from 65535.99999999995 to 65536. The calculation is performed correctly. However, the result is incorrectly shown as 100001.

and is contradicted by the subsequent statement :

Back to the top

MORE INFORMATION

To resolve this problem, apply this hotfix. After you apply the hotfix, recalculate the spreadsheets that are in manual calculation mode. When you do this, the values in the spreadsheet will be updated to the correct values.

Why, IF the calculation is done correctly, one should "recalculate the spreadsheets that are in manual calculation mode" , really escapes me and is the final (unneeded) confirmation that the bug affected calculation and not display of wrong values only.

As a reader plainly put it here:

http://blogs.msdn.com/excel/archive/2007/0...sue-update.aspx

# re: Calculation Issue Update

Thursday, October 11, 2007 5:51 PM by ScottB

@humanbeing:

>I´m a doctor, yesterday I calculated a dosage

>for my patient (yes, it´s your son!) in excel

>as 77.1kg*850=100000mg. He´s dead, you don´t

>know it yet. 65535mg would save his life.

I'd be more worried about being off by a factor of a million (kg/mg) than I would by being off by around a factor of 1.5.

It seems to me a very serious bug, and I find the "official silence" or "understatements" by MS, definitely inappropriate.

Another thing that really makes me wonder is the sheer size of the hotfix, about 34 Mbytes and the number of files involved!

jaclaz

[spacesurfer]

Nothing beats the good old long hand multiplication and division.

Then again, we would be calculating forever!

Oh, the benefits vs disadvantages.