Precision Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1745321%22%20slang%3D%22en-US%22%3EPrecision%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1745321%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20type%20the%20number%26nbsp%3B238875183461%2C81393%20excel%20store%20the%20value%20in%20the%20cell%20as%26nbsp%3B238875183461%2C81300%20why%20this%20happens%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1745321%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1745424%22%20slang%3D%22de-DE%22%3ESubject%3A%20Precision%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1745424%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F820407%22%20target%3D%22_blank%22%3E%40RogerDF%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CH1%20id%3D%22toc-hId-556105869%22%20id%3D%22toc-hId-556105869%22%20id%3D%22toc-hId-556105869%22%20id%3D%22toc-hId-556105869%22%20id%3D%22toc-hId-556105869%22%3EDisplay%20or%20hide%20zero%20values%3C%2FH1%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fdisplay-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9e476b03%3Fui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fdisplay-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9e476b03%3Fui%3Den-us%26amp%3Brs%3Den-us%26amp%3Bad%3Dus%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewould%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1745425%22%20slang%3D%22de-DE%22%3ESubject%3A%20Precision%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1745425%22%20slang%3D%22de-DE%22%3EIf%20I%20may%20also%20recommend%20you%20add%20a%20file%20(without%20sensitive%20data)%20and%20describe%20your%20plans%20on%20it.%20This%20means%20that%20you%20can%20be%20helped%20more%20quickly%20and%20the%20best%20possible%20solution%20tailored%20to%20your%20needs%20can%20be%20offered.%20So%20everyone%20is%20helped.%3CBR%20%2F%3EHelp%20to%20be%20helped.%20%3CBR%20%2F%3E%3CBR%20%2F%3E%20*%20It's%20always%20good%20to%20add%20your%20operating%20system%20and%20Excel%20version.%3CBR%20%2F%3E%3CBR%20%2F%3ENikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1745819%22%20slang%3D%22en-US%22%3ERe%3A%20Precision%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1745819%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F820407%22%20target%3D%22_blank%22%3E%40RogerDF%3C%2FA%3E%26nbsp%3BYou%20can%20read%20more%20about%20floating%20point%20precision%20here.%20Excel%20stores%20numbers%20up%20to%20a%20max%20of%2015%20digits.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fmicrosoft-365%2Fblog%2F2008%2F04%2F10%2Funderstanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers%2F%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fmicrosoft-365%2Fblog%2F2008%2F04%2F10%2Funderstanding-floating-point-precision-aka-why-does-excel-give-me-seemingly-wrong-answers%2F%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi,

 

When I type the number 238875183461,81393 excel store the value in the cell as 238875183461,81300 why this happens?

4 Replies
Highlighted

@RogerDF 

 

 

Display or hide zero values

https://support.microsoft.com/en-us/office/display-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9...

 

would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

Highlighted
If I may also recommend you add a file (without sensitive data) and describe your plans on it. This means that you can be helped more quickly and the best possible solution tailored to your needs can be offered. So everyone is helped.
Help to be helped.

* It's always good to add your operating system and Excel version.

Nikolino
I know I don't know anything (Socrates)
Highlighted

@RogerDF You can read more about floating point precision here. Excel stores numbers up to a max of 15 digits.

https://www.microsoft.com/en-us/microsoft-365/blog/2008/04/10/understanding-floating-point-precision... 

Highlighted

@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 [1] 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 [2].)

 

A2: =vbvalue("238875183461.81393")

A3: =VALUE("238875183461.81393")

A4: =MATCH(A2,A3,0)

 

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

 

 

-----

[1] 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.

 

[2]

Function vbValue(s As String) As Double
vbValue = CDbl(s)
End Function