Excel 2007 multiplication error
Thread Starter
Everything is under control.
Joined: Jul 2001
Posts: 437
Likes: 0
From: Washington, D.C.
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
100000 !!!"
http://shorterlink.com/?ZATE34
Administrator
Joined: Mar 2001
Aviation Qualifications: PPL
Posts: 8,121
Likes: 686
From: Twickenham, home of rugby
Nothing that MS do with the latest generation of products (Vista, IE7, Office 2007) really surprises me any more! I am dreading server 2008 
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
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
Hovering AND talking
Joined: Feb 2003
Posts: 5,711
Likes: 1
From: Propping up bars in the Lands of D H Lawrence and Bishop Bonner
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
Cheers
Whirls
Don Quixote Impersonator
Joined: Jul 1999
Posts: 3,402
Likes: 0
From: Australia
Bugger.
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.
I run all sorts of checksums, audits and rule of thumb checks, is there a fix???
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.
I run all sorts of checksums, audits and rule of thumb checks, is there a fix???
Per Ardua ad Astraeus
Joined: Mar 2000
Posts: 18,575
Likes: 4
From: UK
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..................
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..................
Joined: Apr 2002
Posts: 199
Likes: 6
From: Forest of Dean
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!
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!
Joined: Feb 2007
Posts: 755
Likes: 26
From: Dublin, Ireland. (No, I just live here.)
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.
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.
Administrator
Joined: Mar 2001
Aviation Qualifications: PPL
Posts: 8,121
Likes: 686
From: Twickenham, home of rugby
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
Here's chapter and verse from MS website:
http://blogs.msdn.com/excel/archive/...ue-update.aspx
SD
Joined: Jun 2001
Posts: 4,369
Likes: 3
From: UK.
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)
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)
