excel nuts

Excel stores numbers in a double precision format which allows 15 significant digits. In other words, up to 15 digits (decimal position is irrelevant) can be entered and Excel will remember and display the entered value in its entirety (depending on formatting.) If more than 15 digits are entered then Excel will truncate the entered number to 15 digits before storing it. Double precision is used for all numbers, even if just the numeral 1 is entered in a cell, and regardless of how the cell is formatted.

When that double precision number is moved into a VBA single precision variable then only the 7 most significant digits are captured and the remaining 8 are dropped. If a cell value of 1.23456789012345 is moved to a single precision variable then that value becomes approximately 1.234568.

When the value is moved from a single precision number back to a double precision number then superfluous digits are introduced. The reason is illusive without an understanding of how the numbers are actually stored. Numbers are stored in a binary form using either four bytes for a single precision number or eight bytes for a double precision number. In the single precision number approximately one byte is used for the exponent (power of 10) and in the double precision number approximately two bytes are used. So effectively there are about 3 bytes of precision in the single and 6.5 bytes of precision in the double. The superfluous digits are introduced because Excel and VB erroneously pad the internal hexadecimal representation of the value with zeros instead of translating it to decimal, padding with zeros, and reconverting back to hexadecimal. Excel and VB operate this way for two reasons: 1) it is much faster to just truncate and pad the hexadecimal form of the number and 2) most users of Excel don't care about the introduction of such insignificant errors.

If the 6.5 bytes of a double precision number are stored in binary as 7048861DDF791 then, when converted to a single precision number the stored value becomes 704886 (about 3 bytes). When the single precision number is converted back to a double the stored value becomes 7048860000000. Translated into decimal values these three hexadecimal values are equivalent to:

7048861DDF791 -> 1.23456789012345
704886 -> 1.234568
7048860000000 -> 1.23456788063049

(Note that the above hexadecimal values are not true representations of the decimal numbers and are created purely for demonstration purposes.)

Are the three values the same? That depends on how many significant digits are required. If plotting a trajectory to a distant star then 10 or 15 digits might be required and such a conversion would cause problems. If recording units sold then only need 5 or 6 significant digits are required and such a conversion is insignificant. In other words, whether a company sold 10 or 11 units is interesting, but whether the company sold 6,243,123 or 6,243,124 units is not. So for the purposes of analyzing units sold in a retail environment, the above three values are the same.

This behavior can result in what appears to be digits appearing from nowhere. For example, a value entered as 20.62 in a cell will display as 20.6200008392333 when converted to a single and back to a double.

The superfluous digits can easily be avoided by only using double variables when moving numbers to and from Excel cells and when performing calculations. A correct conversion from a single to a double can be done by converting the value to a string and appending zeros:

   Dim SingleVariable As Single
   Dim DoubleVariable As Double
   SingleVariable = [A1]
   DoubleVariable = CStr(SingleVariable) & "00000000"
   [A2] = DoubleVariable

But this method is crude at best, especially when considering that the double variable and a double conversion function are readily available.

Excel and VB use IEEE 32 and 64 bit floating point number formats. For more information on how double precision numbers are stored see http://www.ee.unb.ca/tervo/ee6373/IEEE64.htm. To create actual hexadecimal representations of 32 and 64 bit floating point numbers see http://babbage.cs.qc.edu/courses/cs341/IEEE-754.html.

Kevin

Thanks for the reply.  Is there a dumbed down version or explanation?  Or a simple solution I can implement to force those cells to store the number as I have typed it, as a 16 digit simple number?

I don't see why Setting it as text wouldn't work

The dumbed down version is that you can't enter more than 15 digits in a cell and have it remain a number.

You can change the format to text and enter the 16 digit number but it will be stored as text and not a number.

Kevin

Actually, you can enter more than 15 digits but it will be truncated.

Kevin

Ok, thanks, I will surrender and figure out something else. Good to know I am not simply losing my mind.

Please consider the need to keep those digits. What is the number you are entering?

Kevin

I think I will just add a few periods after the number to keep it displaying as a number.  It's not a big deal, was just driving me nuts trying to figure out what excel was doing behind the scene.

Now you know. But please consider that, no matter what you are measuring, more than about 10 digits is just not necessary. Whatever those digits are they are probably bogus any way. There isn't a tool in existence than can measure to more than about 10 significant digits.

Kevin