F
fridu
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.
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.