Oct 05 2020 05:31 AM
Oct 05 2020 05:58 AM
would be happy to know if I could help.
I know I don't know anything (Socrates)
Oct 05 2020 05:59 AM
Oct 05 2020 07:34 AM
@RogerDF You can read more about floating point precision here. Excel stores numbers up to a max of 15 digits.
Oct 05 2020 01:13 PM - edited Oct 05 2020 02:02 PM
@Riny_van_Eekelen wrote: ``Excel stores numbers up to a max of 15 digits``.
That is incorrect.
Excel does interpret only the first 15 digits when entered, and Excel does format (display) only the first 15 digits, substituting zero for any digits to the right in both cases.
But Excel "stores" values in memory  with the full precision of 64-bit binary floating-point, which can be many more than 15 significant digits when converted to decimal.
In contrast, VBA interprets all digits when entered. (However, VBA, too, formats only the first 15 digits).
The difference is demonstrated below. (See the VBA function "vbvalue" in footnote .)
A4 displays #N/A because the binary values do not match, despite appearances sometimes. But in this case, even the appearances differ. A2 displays 238875183461.814, but A3 displays 238875183461.813 .
A3 is indeed the binary value of 238875183461.813. This can be demonstrated by entering 238875183461.813 into A5, then noting that MATCH(A3,A5,0) returns 1, indicating an exact binary match.
In contrast, A2 is not the binary value of 238875183461.814. This can be demonstrated by entering 238875183461.814 into A7, then noting that MATCH(A2,A7,0) returns #N/A, indicating that the binary values do not match.
In fact, the exact decimal representation of the binary value in A2 is 238875183461.813,934326171875, whereas the exact decimal representation of the binary value in A7 is 238875183461.813,995361328125.
However, it is only a coincidence that the binary value in A2 converts to the same 17-digit decimal number, when rounded, that we entered. For example, vbvalue("238875183461.81392") and vbvalue("238875183461.81394") also convert to the binary value that is represented exactly by 238875183461.813,934326171875, which rounds to 238875183461.81393 .
BTW, the exact decimal representation of the binary values in A3 and A5 is 238875183461.812,98828125.
(I use period for the decimal point and comma to demarcate the first 15 significant digits.)
 Technically, Excel does "store" values with up to 17 digits, not 15 digits, in an xlsx or xlsm file. But that is only because 17 decimal digits are sufficient to convert to the original binary value without losing binary precision. The exact decimal representation of the binary value is not exactly the 17-digit number.
And as demonstrated above, several 17-digit decimal representations might convert to the same binary value. However, a binary value is converted to only one 17-digit decimal representation (and possibly many more digits), namely the exact sum of 53 consecutive powers of 2.
Function vbValue(s As String) As Double
vbValue = CDbl(s)