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
Power Excel With MrExcel - 2017 Edition

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.