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

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.