Solved

excel nuts

Posted on 2011-03-01
10
344 Views
Last Modified: 2012-05-11
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
Comment
Question by:rodynetwork
  • 6
  • 3
10 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
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

by:rodynetwork
Comment Utility
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 38

Expert Comment

by:Aaron Tomosky
Comment Utility
I don't see why Setting it as text wouldn't work
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
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

by:zorvek (Kevin Jones)
Comment Utility
Actually, you can enter more than 15 digits but it will be truncated.

Kevin
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
Comment Utility
>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

by:rodynetwork
Comment Utility
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

by:zorvek (Kevin Jones)
Comment Utility
Please consider the need to keep those digits. What is the number you are entering?

Kevin
0
 

Author Comment

by:rodynetwork
Comment Utility
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

by:zorvek (Kevin Jones)
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now