Solved

# excel nuts

Posted on 2011-03-01
351 Views
OMG I think i'm going to lose my mind. I am trying to enter a 16 digit number into a cell in excel and it keeps converting it into  a bizarre number.  Example, if I enter 5555000044446666 as soon as I tab out of that cell, it turns into 5.555E+15.  I have tried "format cells" using General, Number, Text and everything else and it keeps doing this.  What the crap?
0
Question by:rodynetwork
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 6
• 3

LVL 81

Expert Comment

ID: 35013675
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
0

Author Comment

ID: 35013705
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?
0

LVL 39

Expert Comment

ID: 35013716
I don't see why Setting it as text wouldn't work
0

LVL 81

Expert Comment

ID: 35013717
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
0

LVL 81

Expert Comment

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

Kevin
0

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 500 total points
ID: 35013735
>I don't see why Setting it as text wouldn't work

You can use the value in calculations but Excel will truncate insignificant digits which is a misrepresentation. Also, you can't format a text string should you want to display, for example, commas.

Kevin
0

Author Comment

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

LVL 81

Expert Comment

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

Kevin
0

Author Comment

ID: 35013771
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.
0

LVL 81

Expert Comment

ID: 35013786
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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Title # Comments Views Activity
excel 2013 keeps 4 29
Modify Text File with Excel Macro 13 44
VBA Kill file but send it to recycle bin, Not permanently delete it. 26 40
Excel - IF criterion 2 19
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
###### Suggested Courses
Course of the Month8 days, 17 hours left to enroll

#### 738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.