How precise is Excel?

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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):

Excel%20PI%20precision - How precise is Excel?

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.

subs profile e1563205311409 - How precise is Excel?
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address