17 or 15 digits of precision

March 02, 2018 - by Bill Jelen

There is an ugly calculation error that has been appearing in Excel. It seems that the problem goes deep into the Excel calculation engine and won't be easy to fix.

At the core of the problem is a simple fact: Excel stores 15 digits of precision in one cell. You can have numbers that have 20 digits, but any digits between the last significant digit and the decimal place must be zero.

I've seen two cases recently where Excel's calculation engine was returning the wrong results. When I dug in to the problem and looked at the underlying XML, I was surprised to see that Excel was secretly storing 17 digits in the XML.

The problem is that Excel will only display 15 digits. So you think that you have a number stored as 0.123456789012345 but it is really stored as 0.12345678901234567.

You can't see those last two digits. And most of Excel's functions are ignoring those last two digits. If *all* of the functions were ignoring the last two digits, we wouldn't have a problem. But so far, sorting, RANK, and FREQUENCY are using all 17 digits.

Below is a well-known trick for ranking cells. If you need every rank to appear exactly once, you can combine RANK and COUNTIF. In the image below, Claire, Flo, Ivana, and Lucy are tied at 115%. Using the RANK+COUNTIF formula, they should be ranked 5, 6, 7, and 8.

But the formula fails. Two rows are ranked as 7. That never happens. Four formulas in column D make sure that the 115% in B6, B9, B12, and B15 are the same. The =B6=B15 formula reports that both cells contain the same data.

As I tried to isolate the problem, look at just the RANK function. It should report a 4-way tie at 4th for the people with 115%. But somehow, Lucy in Row 15 is ranked ahead of the other three.

To figure it out, I sent out a request for help to the other Excel MVP's. Jan Karel Pieterse cracked open the Excel file and looked in the XML. In the XML, you can see that they are storing 17 digits of precision. The four cells that look like a tie in Excel are not tied in the XML. One of the 115% is stored as 1.1500000000000001 and the others are 1.1499999999999999.

So far, sorting, ranking, and the FREQUENCY function are using the extra digits. Why is that a problem? Because we count on RANK and COUNTIF to both use the same number of digits. With one function using 15 digits and the other using 17 digits, you have a problem.

For now, the solution seems to be converting all of your answers using =ROUND(A4,15).

Every Friday, I examine a bug or other fishy behavior in Excel. This calculation error is hard to detect and qualifies as a big fish.

Title Photo: ArtisticOperations / pixabay