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