4 ms·
Almost all of these are issues with floating point in general, I'd say. Some are with naive implementations of Excel functions, fair enough (for example varianc
by FabHK 5y ago
Almost all of these are issues with floating point in general, I'd say. Some are with naive implementations of Excel functions, fair enough (for example variance as (sum x_i^2/n - (sum x_i/n)^2) that are indeed better implemented in R or Julia.
IEEE 754 just has unintuitive properties.
Kahan (the "father of IEEE 754") has a rant (among many others) about Excel as well, and how it tries to hide some of the floating point complexities more or less successfully:
Floating-Point Arithmetic Besieged by “Business Decisions”
https://people.eecs.berkeley.edu/~wkahan/ARITH_17.pdf https://people.eecs.berkeley.edu/~wkahan/ARITH_17.pdf
- belter 5y agoBut you also have issues like 'Catastrophic cancellation': https://carolomeetsbarolo.wordpress.com/2012/07/20/catastrophic-cancellation-in-action/ https://carolomeetsbarolo.wordpress.com/2012/07/20/catastrop... Or: "OOPS XL Did It Again" https://carolomeetsbarolo.wordpress.com/2014/06/22/oops-xl-did-it-again/ https://carolomeetsbarolo.wordpress.com/2014/06/22/oops-xl-d... From the Wikipedia article: "Although Excel can display 30 decimal places, its precision for a specified number is confined to 15 significant figures, and calculations may have an accuracy that is even less due to five issues: round off,truncation, and binary storage, accumulation of the deviations of the operands in calculations, and worst: cancellation at subtractions resp. 'Catastrophic cancellation' at subtraction of values with similar magnitude."
- FabHK 5y agoThat's an issue inherent in floating point computations with fixed precision (such as IEEE 754). julia> 1e20 + 1000 - 1e20 0.0 julia> 1e20 + 10000 - 1e20 16384.0 Python 3.9.6 (default, Jun 28 2021, 19:24:41) >>> 1e20 + 1000 - 1e20 0.0 >>> 1e20 + 10000 - 1e20 16384.0