Multiplication error in excel 2007

If you multiply 850 by 77.1 in Excel 2007, the result will be 100,000, not 65535, as expected. Moreover, if we add 1 (= A1 + 1), the result will be 100001, and if we take away, then 65534. There are many cases when Excel 2007 is wrong:



= 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 limited. If you add not 1, but any other number, the result will be correct. However, when calculating fairly complex tables, this error can unpleasantly manifest itself.



According to unconfirmed data, 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 is again 1 less. At the same time, Dec2Hex (850 * 77.1 + 2) = 10001, i.e. the calculation is correct.



2. Excel does not always correctly calculate the standard deviation. For example, if you 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 counts the standard deviation. For example, for 9989999999223 we get 123575.8667. If you slightly change this value of x (within 0.05), then the standard deviation will change by a very significant amount.



Based on microsoft.public.excel .



PS Debut on Habré :)