Big numbers rounded in Excel

Excel and really large numbers – they don’t play nice together.

When we wrote about large prime numbers in Excel we forgot that Excel doesn’t do a good (some might say honest) job with large numbers.

Sorry about that – mea culpa.

Excel is fine with integers up to 15 digits but once you go over that, it doesn’t error but quietly rounds values. In other words a 16 digit number or more stops being accurate.

Microsoft hides that inaccuracy by displaying the number in scientific notation in its General format which has the effect of obscuring the rounding that’s going on.

As we mentioned in our previous article, this isn’t to diss Excel. The numbers we’re talking about are way bigger than most people would ever need. Nonetheless, it’s something Excel users should be aware of and, in our opinion, should be made clearer to users. Excel is changing the number entered into and, for a spreadsheet, that’s hardly ‘best practice’ regardless of the reasons.

Windows 10 from people 'in the know'

A detailed and independent look at Windows 10, especially written for the many people who use Microsoft Office.

Fully up-to-date with coverage of the Anniversary 2016 major update of Windows 10.

This 670 page book shows you important features and details for all serious Windows 10 users.


Rounding

Have a look at this worksheet of large primes. The second column shows the text rendering of the number or Mersenne prime formula used to generate the number. The third and fourth columns show that number in Excel’s General format then as a comma-separated integer.

Large primes rounded in Excel image from Big numbers rounded in Excel at Office-Watch.com

A few things to note:



  • The 15 and 16 digit primes near the top read as being the same in General format ie 1E+15 when they are different numbers.
  • The 16 digit prime is rounded by Excel (ending with 030 instead of 037). That’s the effect of Excel’s hidden rounding of large values. When you type ‘=1000000000000037’ into the cell, Excel changes it immediately and without warning to ‘=1000000000000030’
  • The same applies to Mersenne 61 and higher. Notice all the zeros at the end.

    • The correct value for Mersenne 61 is 2,305,843,009,213,693,951 – difference of a mere 3,951 .

  • The last two Mersenne’s (521 and 607) have too many digits to display (157 and 183 digits respectively)

Displaying large numbers

If you do need to display a large number in Excel, it’s best to do it as text. You can’t use it as a number (if you do, it will be rounded) but it can be displayed just like any other text.

Large number display in Excel image from Big numbers rounded in Excel at Office-Watch.com

In an Excel cell, type an apostrophe then type in (or paste) the number. The apostrophe tells Excel that the following is to be treated as text instead of it attempting to format it automatically.