Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / New Users / October 2007

Tip: Looking for answers? Try searching our database.

Multiplication bug in Excel 2003 suspicion

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
fridu@gmx.net - 03 Oct 2007 11:07 GMT
The Vista/Excel 2007 multiplication bug makes one think ... and play a
little with the WinXP/Excel 2003 too.
Try this (best is column-wise for systematic checking):

METHOD

- DIVIDEND / DIVISOR = QUOTIENT ; QUOTIENT * DIVISOR = PRODUCT1
- Transform PRODUCT1 to hex number (dec2hex) and then again to decimal
number (hex2dec) which gives PRODUCT2.
- Withdraw PRODUCT2 from  which gives DELTA.
- Fill the column for the DIVISOR with 65535 and DIVIDEND with values
1,2.3...,65535.
- Copy the table an open the same for the 65536 to 131070 DIVISOR
range

RESULTS:

As known in common algebra, DELTA should always be 0, but sometimes
the delta is not 0 but 1! (eg at x=13, 26, 49...). Which means the
representation of the number may be correct but the calculated value
is different from the representation (exactly the opposite from the
descripted 2007 bug).

For a=65535 i get 10'028 faultive deltas in the range from 1 to 65535
and 9'963 faultive deltas in the range from 65536 to 131'070 which
gives a fault percentage of a modest 15.25%.

I tried a randomized DIVIDEND in the same range and still get a sum of
DELTAS highly superior to 0!!!

PROVISIONAL CONCLUSION

- Excel seems to have a quite systematic problem with floating point
operations.
- Pattern recongnition may be an interesting approach to find the
solution.
- Overall trust in Excel as an calculation tool is severly damaged.
- Waiting for indipendent confirmations of my suspicion (don't ask for
the file, even compressed it's 20MBs :-).
- Then tell the product owner he might have a little bit more than
suspected of the same problem.
Niek Otten - 03 Oct 2007 12:16 GMT
This is common knowledge and has been discussed hundreds of times in these groups:

http://support.microsoft.com/kb/78113

Signature

Kind regards,

Niek Otten
Microsoft MVP - Excel

| The Vista/Excel 2007 multiplication bug makes one think ... and play a
| little with the WinXP/Excel 2003 too.
[quoted text clipped - 37 lines]
| - Then tell the product owner he might have a little bit more than
| suspected of the same problem.
adam.vero@gmail.com - 03 Oct 2007 17:50 GMT
On 3 Oct, 11:07, fr...@gmx.net wrote:
> The Vista/Excel 2007 multiplication bug makes one think ... and play a
> little with the WinXP/Excel 2003 too.
[quoted text clipped - 5 lines]
> - Transform PRODUCT1 to hex number (dec2hex) and then again to decimal
> number (hex2dec) which gives PRODUCT2.

That's your error, right there: the result of the division and
multiplication is not an integer because of the tiny inaccuracy of
floating point math (in principle as a design limitation, not just in
Excel).
DEC2HEX only works on integers, anything else it truncates first.
so HEX2DEC(DEC2HEX(whatever)) is the same as simply truncating the
original number to an integer, which in many cases is taking away
0.9999... from the original value
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.