How accurate is Excel compared to universal needs.
On page 45 of the Sept/Oct 2013 Mental Floss magazine is a small item ‘Why do we care so much about the digits if Pi’ which includes this trivia:
“The computers that keep the International Space Station humming round off pi at a crude 16 digits.
When scientists calculate the value of the speed of light and other universal constants, they use 32 digits.
Heck, you can even measure the circumference of the known universe with a margin of error no bigger than the radius of a hydrogen atom–it’s a great party trick–using a measly 39 digits. “
The last one we’ve heard before, from no less an authority than Marcus de Sautoy, a Professor of Mathematics at Oxford.
So how does Excel shape up? Can Excel do calculations to a scientific precision?
No.
Excel is limited to 15 digit precision. Office-Watch.com has mentioned this before for large numbers.
Microsoft says
“This limitation is a direct result of strictly following the IEEE 754 specification and is not a limitation of Excel. This level of precision is found in other spreadsheet programs as well“.
But that’s an excuse since Microsoft invokes the ‘754’ standard when it suits them and ignores the same standard when it doesn’t. See KB78113 where Redmond lists where they do and do not comply.
Using the famous maths constant PI as an example. We typed in PI to 39 decimal places and used the PI() function to return the same value from Excel itself. Here’s the results shown to 16 decimal places (Format: number with 16 decimal places):
If you type any number to more than 15 digits precision, Excel will cut it back to 15 digits as soon as you press Enter. There’s no warning of the change, not even an ‘audit’ or error flag.
15 digits is enough for most purposes but we can’t help think (not for the first time) that Microsoft could do much better. Standard computers have much greater power and facilities than when Excel standards and the ‘754’ standard was created. Most of us have 64-bit processors, not the 16-bit chips common years ago.
Sadly, the commercial reality is that increased precision would cost a lot of Microsoft development money. It’s an investment Microsoft feels it doesn’t have to bother with.
If you think greater precision isn’t possible in Excel – consider that you can use the xlPrecision add-in to get 32,767 digit precision. If an Excel supplement can do it, Excel itself could do the same and probably better or faster.
See Also
- Excel bug: Row Insertion and cell ranges
- Big numbers rounded in Excel
- Workarounds for Excel’s addition problems
- Excel addition problems – bug or not?
- Microsoft’s ‘Get out of jail free’ card for Excel
- More Excel addition strangeness
- Excel SUM anomaly
