Multiplication Error in Excel 2007
If you multiply 850 by 77.1 in Excel 2007, the result is 100,000, not 65535, as expected. Moreover, if you add 1 (= A1 + 1), the result will be 100001, and if you take it away, then 65534. There are a lot of cases when Excel 2007 makes a mistake:
= 5.1 * 12850
= 10.2 * 6425
= 20.4 * 3212.5
= 40.8 * 1606.25
= 77.1 * 850
= 154.2 * 425
= 212.5 * 308.4
= 308.4 * 212.5
= 425 * 154.2
= 2 * 10.2 * 3212.5
, etc.
Fortunately, the error manifests itself quite limitedly. If you add not 1, but any other number, then the result will be correct. However, when calculating sufficiently complex tables, this error can be unpleasant.
According to unconfirmed reports, the patch has already been created and is available to a narrow circle of MSDN subscribers (high-level MSDN subscribers). However, there is no official information on the site yet.
Previous versions of Excel do not contain this error.
Some of the other features of Excel:
1. Excel 2003 normally multiplies 850 by 77.1, but if you translate the result (65535) into the hexadecimal system using Dec2Hex, the result will be FFFE, which is 1 less than the correct answer. Accordingly, Dec2Hex (850 * 77.1 + 1) will be FFFF, which again is 1 less. At the same time, Dec2Hex (850 * 77.1 + 2) = 10001, i.e. The calculation goes right.
2. Excel does not always correctly calculate the standard deviation. For example, if we take an arbitrary x and calculate the standard deviation for the sequence x + 1, x + 2, ..., x + 10, then the result should be 3.027650354. However, for some x, Excel incorrectly calculates the standard deviation. For example, for 9989999999223 we get 123575.8667. If this value of x is slightly changed (within 0.05), then the standard deviation will change by a very significant amount.
Based on materials from microsoft.public.excel .
PS Debut on a habr :)
= 5.1 * 12850
= 10.2 * 6425
= 20.4 * 3212.5
= 40.8 * 1606.25
= 77.1 * 850
= 154.2 * 425
= 212.5 * 308.4
= 308.4 * 212.5
= 425 * 154.2
= 2 * 10.2 * 3212.5
, etc.
Fortunately, the error manifests itself quite limitedly. If you add not 1, but any other number, then the result will be correct. However, when calculating sufficiently complex tables, this error can be unpleasant.
According to unconfirmed reports, the patch has already been created and is available to a narrow circle of MSDN subscribers (high-level MSDN subscribers). However, there is no official information on the site yet.
Previous versions of Excel do not contain this error.
Some of the other features of Excel:
1. Excel 2003 normally multiplies 850 by 77.1, but if you translate the result (65535) into the hexadecimal system using Dec2Hex, the result will be FFFE, which is 1 less than the correct answer. Accordingly, Dec2Hex (850 * 77.1 + 1) will be FFFF, which again is 1 less. At the same time, Dec2Hex (850 * 77.1 + 2) = 10001, i.e. The calculation goes right.
2. Excel does not always correctly calculate the standard deviation. For example, if we take an arbitrary x and calculate the standard deviation for the sequence x + 1, x + 2, ..., x + 10, then the result should be 3.027650354. However, for some x, Excel incorrectly calculates the standard deviation. For example, for 9989999999223 we get 123575.8667. If this value of x is slightly changed (within 0.05), then the standard deviation will change by a very significant amount.
Based on materials from microsoft.public.excel .
PS Debut on a habr :)