title: Excel - why two equal integers have non-zero difference?
body: <p>I have <code>=2*10^15</code> in one cell which evaluates to <code>2,000,000,000,000,000</code> and I have <code>=2+2*10^15</code> in another cell which also evaluates to <code>2,000,000,000,000,000</code>, which is fine since the precision is only 15 digits. But when I then subtract one from another, I get back <code>2</code>, which I dont understand where it came from. As far as I can tell <code>2*10^15</code>has an exact binary representation, so I would have thought that when adding <code>2</code> to it, it just  "gets dropped". But it seems that my understanding is incorrect. </p>
  
  <p>Actually, as far as I can tell, the binary representation of <code>2E+15</code> has <code>1</code> in places corresponding to powers of <code>2</code> equal to <code>{50,49,48,44,43,41,39,38,37,36,35,34,32,30,27,24,23,19,18,16}</code>, then why can't we just add another bit in place correspondign to power <code>1</code> and have an exact representation of <code>2+2E+15</code>?</p>
  
  <p>Furthermore, if instead of <code>2</code> I add <code>1</code>, then the difference between <code>2*10^15</code> and <code>1+2*10^15</code> is <code>0</code> in Excel spreadsheet, but if I do the same calculation in Excel VBA, then I get back 1. What is that about?</p>
  
  <p>I have read (several times) "What Every Computer Scientist Should Know About Floating-Point Arithmetic" but the discussion there seems to be focused on decimals rather than integers or I am just not getting it.  </p>
  
  <p><strong>Add 1</strong></p>
  
  <p>I remembered that a while ago I wrote a function in VBA to get the binary representation of a number stored by Excel (the function could be found here
  <a href="https://stackoverflow.com/questions/31909506/extract-mantissa-exponent-and-sign-data-from-ieee-754-double-in-vba/31922301#31922301">Extract mantissa, exponent and sign data from IEEE-754 double in VBA</a>). So if I apply that function to <code>2 + 2E+15</code> it does return a binary with <code>1</code> in the 4th last place in mantissa (not sure why 4th, though) which is not there when the function is applied to <code>2E+15</code>, so it does seem that Excel simply choses to display number with 15 digits of precision even though the underlying stored floating number has a larger precision.</p>
  
  <p><strong>Add 2</strong></p>
  
  <p>It turns out that the reason that the extra bit in the mantissa of <code>2 + 2E+15</code> is in the 4th place (instead of 2nd) is that the mantissa gets "normalised" by dropping the leading (leftmost) bit (since it is always 1) and adjusting the length of the mantissa to 52 bits by shifting everything left which adds 2 0s at the end to the "simple" binary representation so that
  <code>1110-0011-0101-1111-1010-1001-0011-0001-1010-0000-0000-0000-010</code>
  becomes
  <code>1100-0110-1011-1111-0101-0010-0110-0011-0100-0000-0000-0000-1000</code>.</p>
  
  <p>It seems that due to this normalisation of the mantissa, the precision in the floating point double representation is actually 18 decimal places and, for example, the number <code>1.75 + 2E+15</code> can be represented exactly by mantissa <code>1100-0110-1011-1111-0101-0010-0110-0011-0100-0000-0000-0000-0111</code>.</p>
  
  <p>Just need to figure out what is going on with the exponent... There is something called "biasing" being done to it...</p>