Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 16:33, 28 September 2024

Revision as of 04:33, 9 March 2025

Line 114:

}}

1. The shortcomings in the {{code|{{=}} 1 + x - 1}} tasks are a combination of 'fp-math weaknesses' and 'how Excel handles it', especially Excel's rounding. Excel does some rounding and / or 'snap to zero' for most of its results, in average chopping the last 3 bits of the IEEE double representation. This behavior can be switched ofoff by setting the formula in parentheses: {{code|{{=}} ( 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 {{code|1}}, and the last the {{code|2^-52}}.

2. It is 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 do not have a clean finite representation in binary they will suffer from 'round off' and 'cancellation' in tasks like the above.