Content deleted Content added
No edit summary |
added some clarification about soures of fp-errors vs. rounding in excel |
||
Line 53:
*{{cite book |title=A guide to Microsoft Excel 2007 for scientists and engineers |author=Bernard V. Liengme |chapter=Mathematical limitations of Excel |page=31 ''ff'' |url=https://books.google.com/books?id=0qDm7uuDmv0C&pg=PA31 |isbn=0-12-374623-X |year=2008 |publisher=Academic Press }}
</ref>
Beforementioned concepts need some clarification, will do it in better detail once i have time for it.
1. the shortcomings in the '= 1 + x - 1' tasks are a combination of 'fp-math weaknesses' and 'how excel handles it', especially excels rounding. excel does some rounding and / or 'snap to zero' for most of it's results, in average chopping the last 3 bits of the IEEE double representation. This behavior can be switched of by setting the formula in parentheses: '= ( 1 + 2^-52 - 1 )'. you will see that even that small value survives. smaller values will pass away as there are only 53 bits to represent the value, for this case 1.0000000000 0000000000 0000000000 0000000000 0000000000 01, the first representing the '1', and the last the '2^-52'.
2. it's not only clean powers of two surviving, but any combination of values constructed of bits which will be within the 53 bits once the decimal 1 is added. as most decimal values don't have a clean finite representation in binary they will suffer from 'round of' and 'cancellation' in tasks like the above.
E.g. decimal 0,1 has the IEEE double representation 0 (1).1001100110011001100110011001100110011001100110011010 times 2^-4, and added to 140737488355328.0 (which is 2^47) will loose all of it's bits except the first two. thus from '= ( 140737488355328.0 + 0,1 - 140737488355328.0) it will come back as 0.09375 calculated with www.weitz.de/ieee (64 bit) as well as in excel with the parentheses around the formula. This effect mostly can be managed by meaningful rounding, which excel doesn't apply, it's up to the user.
Needless to say: other spreadsheets have similar problems, LibreOffice Calc uses a more aggressive rounding, while gnumeric tries to keep precision and make as well the precision as the 'lack of' visible for the user.
===Statistical functions===
Line 94 ⟶ 104:
then <math>C1</math> does not display <math>0</math> as would be expected,
but displays <math>-3.55271E-15</math> instead.
(above is not limited to subtractions, try '=1+1,405*2^-48' in one cell, excel rounds the display to 1,00000000000000000000, and '=0,9+225179982494413*2^-51' in another, same display (in the range above 1 / below 1 the rounding is different, which hits most decimal or binary magnitude changes). above, different rounding for value and display, violates one of the elementary requirements in [https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html Goldberg: 'What Every Computer Scientist Should Know About Floating-Point Arithmetic'] (more or less 'the holy book' of fp-math), there stated: 'it is important to make sure that its use is transparent to the user. For example, on a calculator, if the internal representation of a displayed value is not rounded to the same precision as the display, then the result of further operations will depend on the hidden digits and appear unpredictable to the user' (the problem is not limited to excel, e.g. LibreOffice calc acts similar).
===Round-off error===
Line 102 ⟶ 114:
:<math>x= \frac{-b \pm \sqrt{b^2-4ac} }{2a}. </math>
When one of these roots is very large compared to the other, that is, when the square root is close to the value ''b'', the evaluation of the root corresponding to subtraction of the two terms becomes very inaccurate due to round-off (cancellation?).
It is possible to determine the round-off error by using the [[Taylor series]] formula for the square root:
| |||