MrExcel Publishing
Your One Stop for Excel Tips & Solutions

15 Digits of Precision

July 20, 2017 - by Bill Jelen

15 Digits of Precision

Topics covered in the video below:

  • Fundamental belief: Excel stores 15 digits of precision.
  • How RANK returns duplicates
  • How RANK+COUNTIF eliminates duplicates
  • Mystery where RANK+COUNTIF still returns a duplicate
  • Four cells contain 1.15
  • =A2=A3 shows the cells are equal
  • Ctrl+` shows the cells are equal
  • But the four cells do not sort correctly. Something is different.
  • Change the XLSX to a Zip file
  • Open the XML inside the Zip File
  • What? Excel is storing 17 digits!?!
  • So - is that a good thing? More accurate, right? Maybe 16-digit credit card numbers stored as numbers, right? Hooray?
  • But... while some functions only use 15 digits (COUNTIF and equality tests) others use 17 digits (RANK and sorting). One formula that uses a mix of RANK+COUNTIF will potentially return the wrong answer. What happened to "Recalc or Die"? One workaround, wrap your values to be ranked in =ROUND(A2,14).

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point 2nd Edition

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.