Excel 2007 multiplication error
 

"Simply when you try to multiply 850 by 77.1 excel display the result to be
100000 !!!"


http://shorterlink.com/?ZATE34

Nothing that MS do with the latest generation of products (Vista, IE7, Office 2007) really surprises me any more! I am dreading server 2008 :sad:

I don't have figures to hand, but every new release of OS / Office seems to increase the number of lines of code by a factor of 2 or more, hence more bugs.

This reminds me of the intel floating point debacle with the Pentium 1, way back in 1994! I forget the details, but there was serious concern around the world due to the possibilities of undetected errors in calculations ranging from business modelling and forecasts to academic research and military applications!

Interesting to watch the fall-out, from a safe place! I still use Office XP ;)

SD

Glad to say that I do not have that problem using Apples 'Numbers' on my MAC!

Ha ha, you're right, it does! I'm running XP on a laptop but with Excel 2007 and I get the same result! Thankfully, I have a reliable desktop!

Cheers

Whirls

I'm running Excel 2003, 850 x 77.1 = 65535.

Bugger.:mad: I run some fairly big and complex spreadsheets and now have lost a little bit of confidence in the answers it/they produce.

Office 2007 on XP Pro and I too get 10000.:yuk:

I run all sorts of checksums, audits and rule of thumb checks, is there a fix???:{

http://www.theregister.co.uk/2007/09/26/excel_2007_bug/

MS are now 'aware'! It looks from the link as if the actual number is correct, it is the displayed number that is wrong. If this is so, then your calcs should be ok as long as you do not need to display them..................:confused:

Not reliably so. If you multipy the result of 850*77.1 then the result will probably be correct (not if the multipland will regenerate the display error), so the result multiplied by 2 will display correctly as 131070. But, if you add 1 to the result, then the number displayed is 100001.

Although my work laptop uses Office 2003, I still don't use any features of Word or Excell which were not available in Office 95 or Word 6. My own PCs use OpenOffice Org, but I am considering trying the new Lotus Symphony which is now on a beta download. That, hopefully will be different to the last version which I used in 1987!

Eboy
Chip sets on your pc I fear. Not MS.

Dave

I've been doing a bit more testing here. There are a lot of combinations of factors that give the same result. I agree that the internal representation of the number is still correct i.e. a floating point number very close to 65535, so all subsequent calculations will be fine. As long as you don't actually need any output, on paper or screen. :\

BUT - and there's a big but - this affects Filtering too. So, if you are using Filter on results, you might get false positives (too many results) if you filter for 100000, or false negatives (missing results) if you filter on 65535. According to that blog entry, a fix is currently under test.

Sorry Dave, it is 100% definitely MS Excel 2007 - nothing whatsoever to do with processors or chipsets.

Here's chapter and verse from MS website:

http://blogs.msdn.com/excel/archive/...ue-update.aspx

SD

From Sydney Morning Herald :
Microsoft's Excel 2007 spreadsheet program is going to have to relearn part of its multiplication table.
In a blog post, Microsoft employee David Gainer said that when computer users tried to get Excel 2007 to multiply some pairs of numbers and the result was 65,535, Excel would incorrectly display 100,000 as the answer.
Gainer said Excel makes mistakes multiplying 77.1 by 850, 10.2 by 6425 and 20.4 by 3212.5, but the program appears to be able to handle 16,383.75 times 4.
"Further testing showed a similar phenomenon with 65,536 as well," Gainer wrote Tuesday.
He said Excel was actually performing the calculations correctly, but when it comes time to show the answer on the screen, it messes up.
Gainer said the bug is limited to six numbers from 65,534.99999999995 to 65,535, and six numbers from 65,535.99999999995 to 65,536, and that Microsoft is working hard to fix the problem.
Yes, if you add 1 or even add then subtract 1 the error remains BUT it appears that, if you add then subtract 2 or more the answer displays correctly (no guarantees)

Thanks Saab, I stand corrected. Was thinking a number of years ago Intel had a problem with the carry digit, binary to a integer then to real numbers.

Dave