15 Digits of Precision

July 20, 2017 - by Bill Jelen

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).