4 ms·

I've always been fuzzy on Excel's precision. It's frequently described as preserving or storing fifteen significant figures. I wasn't sure if it rounds before

I've always been fuzzy on Excel's precision. It's frequently described as preserving or storing fifteen significant figures. I wasn't sure if it rounds before storing, or if that's just how it displays. After some experimentation, I've satisfied myself that it can store more than it can display. For example, =PI() displays at most 3.14159265358979, no matter how many digits I ask it to display. If I add 1.999E-15, I get 3.1415926535898. If I type 3.14159265358979 (omitting the invisible trailing 3), then add 1.999E-15, I get 3.14159265358979. In other words, Excel uses double-precision floating point, but never displays more than fifteen significant figures. You may be in for a surprise if, say, you round trip a spreadsheet through CSV.

bitmedley 4y ago
Excel uses IEEE 754 double precision which stores up to 17 significant digits but the Excel UI itself only displays up to 15 significant digits. [0] https://en.wikipedia.org/wiki/Double-precision_floating-point_format#:~:text=If%20an%20IEEE%20754%20double,must%20match%20the%20original%20number https://en.wikipedia.org/wiki/Double-precision_floating-poin.... [1] https://www.mrexcel.com/excel-tips/17-or-15-digits-of-precision/ https://www.mrexcel.com/excel-tips/17-or-15-digits-of-precis... [2] https://www.youtube.com/watch?v=WZfjmbEDbfI https://www.youtube.com/watch?v=WZfjmbEDbfI