Entering 18 digit number in Excel

Dear All,

I am trying to enter a 18 digit number in one cell. Surprisingly, the
lasst two digits are automatically being changed to 00.

The number i tried to enter is - 857013340418093014. The last two
digits i.e. 14 is automatically changed to 00!

Could any one of you please help.

Thanks & Regards,
Jaipal Singal.
0
jaipal1 (4)
5/28/2008 4:11:41 AM
excel 39879 articles. 2 followers. Follow

9 Replies
639 Views

Similar Articles

[PageSpeed] 33

<jaipal1@gmail.com> wrote in message
news:50262a6f-d859-42c2-9171-494650141076@l28g2000prd.googlegroups.com...
> Dear All,
>
> I am trying to enter a 18 digit number in one cell. Surprisingly, the
> lasst two digits are automatically being changed to 00.
>
> The number i tried to enter is - 857013340418093014. The last two
> digits i.e. 14 is automatically changed to 00!
>
> Could any one of you please help.

Excel, like all applications, has to choose an internal data type in which
to store numbers.
Your number has too many significant digits to be accurately stored.


0
kaikow (53)
5/28/2008 6:13:06 AM
On May 28, 11:13=A0am, "Howard Kaikow" <kai...@standards.com> wrote:
> <jaip...@gmail.com> wrote in message
>
> news:50262a6f-d859-42c2-9171-494650141076@l28g2000prd.googlegroups.com...
>
> > Dear All,
>
> > I am trying to enter a 18 digit number in one cell. Surprisingly, the
> > lasst two digits are automatically being changed to 00.
>
> > The number i tried to enter is - 857013340418093014. The last two
> > digits i.e. 14 is automatically changed to 00!
>
> > Could any one of you please help.
>
> Excel, like all applications, has to choose an internal data type in which=

> to store numbers.
> Your number has too many significant digits to be accurately stored.

I am sorry - I did not understand what you mean by - "too many
significant digits" & "internal data type". I thought the data type is
number & at least 18 digits should be generally allowed
0
jaipal1 (4)
5/28/2008 6:22:22 AM
On May 27, 9:11=A0pm, jaip...@gmail.com wrote:
> I am trying to enter a 18 digit number in one cell. Surprisingly,
> the lasst two digits are automatically being changed to 00.

Actually, the last 3 digits.  Excel data entry is limited to 15
significant digits.  By coincidence, your 16th digit is already zero.


> The number i tried to enter is - 857013340418093014.

If you do not need to use the number for computation, you can enter it
as text by prefixing the number with an apostrophe; that is,
'857013340418093014.

If you need a computable number, the best you can do is:

=3D857013340*10^9 + 418093014

However, because of the limitations of the Excel internal
representation -- which is typical of most binary computer
applications -- that will result in the value 857013340418093056 -- 28
more than your number.

(The closest number less than that is 857013340418092928, which 86
less than your number.)
0
joeu2004 (766)
5/28/2008 6:36:58 AM
On May 27, 11:22=A0pm, jaip...@gmail.com wrote:
> I thought the data type is number & at least 18 digits
> should be generally allowed

Whatever gave you that idea?  That is not a provocative comment.  I am
genuinely interested in where you found that misinformation.

In Excel 2003, if you use Help to search for "limits", click on "Excel
specifications and limits", then click on "Calculations
specifications", you will see that the "number precision" is 15
digits.

Although Excel will enforce that limit on data entry and data display,
the internal representation is capable of representing __some__
decimal numbers with more digits.
0
joeu2004 (766)
5/28/2008 6:47:19 AM
On May 28, 11:47=A0am, joeu2004 <joeu2...@hotmail.com> wrote:
> On May 27, 11:22=A0pm, jaip...@gmail.com wrote:
>
> > I thought the data type is number & at least 18 digits
> > should be generally allowed
>
> Whatever gave you that idea? =A0That is not a provocative comment. =A0I am=

> genuinely interested in where you found that misinformation.
>
> In Excel 2003, if you use Help to search for "limits", click on "Excel
> specifications and limits", then click on "Calculations
> specifications", you will see that the "number precision" is 15
> digits.
>
> Although Excel will enforce that limit on data entry and data display,
> the internal representation is capable of representing __some__
> decimal numbers with more digits.

Thanks a lot for your help & information. As i had already mentioned
in my earlier post, i just thought that 18 digits should be allowed.
However, as you have correctly specified, I also checked in help for
limits and got the same answer as posted by you. Thanks again!
0
jaipal1 (4)
5/28/2008 7:00:30 AM
On May 27, 11:00=A0pm, jaip...@gmail.com wrote:
> i just thought that 18 digits should be allowed.
> However, as you have correctly specified, I also
> checked in help for limits and got the same answer
> as posted by you. Thanks again!

You're welcome.  In your defense, I did find a Microsoft web page [1]
that claims that a VB type Double "can hold as many as 18 significant
digits".

That is just plain wrong.  It can represent "as many as" 1074
significant digits in __some__ cases (namely, just one:  the most
fractional digits with no non-fractional non-zero digits).  But VB
Double uses the same internal representation as an Excel number.  The
largest integer that is not a power of 2 and that can be represented
exactly is 2^53 - 1, a 16-digit number.

(But Excel will display only the first 15 digits.)


Endnotes:

[1] http://msdn.microsoft.com/en-us/library/5c53yzyb.aspx
0
joeu2004 (766)
5/28/2008 7:59:50 AM
<jaipal1@gmail.com> wrote in message
news:57dae18b-38ac-4a67-b7de-9b369ac4c8e5@v26g2000prm.googlegroups.com...
I am sorry - I did not understand what you mean by - "too many
significant digits" & "internal data type". I thought the data type is
number & at least 18 digits should be generally allowed

The number of digits that may be used is, in general, determined by the
hardware data types, in this case the Double data type.

In addition, there are data types not tied to the hardware, e.g,, the
Currency type, wfich would handle your numbers.

However, numbers are stored using the Double type, largely fo reasons of
speed.


0
kaikow (53)
5/28/2008 8:32:42 AM
Errata....

On May 27, 11:59=A0pm, I wrote:
>=A0The largest integer that is not a power of 2 and
> that can be represented exactly is 2^53 - 1, a
> 16-digit number.

Obviously I misspoke, having just demonstrated how to represent an 18-
digit integer.  The largest integer that can be presented is a 309-
digit number [1].  But these are special cases.


[1] =3D2*(2^1023 - 2^970)
0
joeu2004 (766)
5/28/2008 8:34:10 AM
For example, see the book 'The Definitive Duide to How computers Do Math",
ISBN 0-471-73278-8.

And see MSFT KB article 42980.

The number of significant digits is limited by the hardware for the Double
type
Excel, as with most ap0plications, just uses the data types provided by the
underlying programming language, in this case, I expect largely C++.


0
kaikow (53)
5/28/2008 8:44:00 AM
Reply:

Similar Artilces:

How do I use line numbering but not have it show on print?
I do not want the line numbers to show in printed material. Meinert wrote: > I do not want the line numbers to show in printed material. Go into the Page Setup dialog and turn off line numbering before you print. If you want it, turn the numbering on again afterward. To automate this, install the following macros in your Normal template (see http://www.gmayor.com/installing_macro.htm if needed): Sub FilePrint() ' intercepts built-in File > Print or Ctrl+P ' and prints without line numbering Dim userLineNum As Boolean With ActiveDocument.PageS...

Auto generating unique number
Hi We would like to customize CRM so that when an opportunity is created, a unique number is generated and this unique number appears throughout the sales process. This number has to be auto generated by CRM so that a user does not have to enter it manually. The number should follow automatically from the previous number. Can this be achieved by creating a workflow rule using a call assembly? Grateful for any assistance. Thanks ...

Excel click through
At work I can double click on a cell that is linked to another spreadsheet and it opens that spreadsheet. How do u turn this on? Does it work in office 2000 or do u need Office pro? Keith -- keithgg ------------------------------------------------------------------------ keithgg's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=29961 View this thread: http://www.excelforum.com/showthread.php?threadid=496590 Try: Tools|Options|Edit Tab uncheck Edit directly in cell keithgg wrote: > > At work I can double click on a cell that is linked to another >...

Excel does not return to previous cell
Hi all, I am running into an problem with my users. We have a Excel 2000 spreadsheet. I know that typically, the sheet/cell that is selected when a user closes the spreadsheet will be the same when a user opens the spreadsheet the next time. However, the problem I am seeing is that the proves true with some users, but not with all users. Some users open the spreadsheet and instead of going back to, say cell A972, it opens to, say cell B4. I would welcome any input on this issue. Thanks, Jeff Averhoff <<<"I know that typically, the sheet/cell that is selected when a user...

how to use a negative number
I have a client that gave me a deposit for a service. I was paid this deposi last year. I now have made a invoice for this service with Money Home and Business 2007. How can I show that I have already received $200 towards this service? Money will not let me use a negative number So how can I do this? Can't say for sure as I've never used the * Business * editions, but you may have to use an income category to categorize the amount. IIRC, it has been reported that the * Business * editions don't allow calling income expense and vice versa. Sadly, next to no users of * B...

Removal of broken Hyperlinks from data copiedfrom a web page in to an Excel Spreadsheet
I have copied over 600 + lines into excel from a web page data base. I have cells which appear as hyperlinks (blue underlined text). When I put the insertion point over the cell, I get the little "hand" and a hyperlink box opens but is blank. I do not want the text in the cells to be hyperlinks nor do I want to open any hyperlinks. I went to the edit menu to select links to remove all but links is grayed out. I can remove each one, one by one, by using the arrow key to move into the cell, doing a right click and select, remove hyperlink. This works, but will not accept multiple...

Limits of Excel 2003
The worksheet have 256 columns. There are some way to increase this columns to 800 Hi no, not possible -- Regards Frank Kabel Frankfurt, Germany "ReneCabrera" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:0afb01c4a258$85bdd990$a501280a@phx.gbl... > The worksheet have 256 columns. There are some way to > increase this columns to 800 ...

Loading data from Excel to Oracle
Hi All, I would like to load an Excel spreadsheet directly into a single table in Oracle. Both the spreadsheet and the table have the same ordering of columns and have compatable data. I am aware that a way to achieve this would be to convert the .xls file into a .csv file and then use sql loader. However, I am interested in doing this in a quicker/easier way directly from Excel, eg. by adding in some functionality and clicking on a new button for loading into the database. We are all able to read from a database in Excel by importing external data. Surely there is a way to change that...

How can I "undo" an entry on a form/subform while still entering data ? 11-18-07
Hi I posted this question a couple of days ago and got 1 response but couldn't read it. Hopefully someone will have and answer. I have a form/subform for entering invoice data. What can I do if I realize that I have already entered the invoice ? Example: I enter the main form data and enter 5 lines of detail on the subform and then I realize that I have already entered this invoice before ? I would love to have a button that I could click that "undoes" the entire entry. How can this be accomplished ? Thanks Mark <mthornblad@gmail.com> wrote in message news:81271...

Excel automation to access a workbook
I want to utililze Excel Automation to obtain properties from an Excel workbook. Given path of the Excel workbook, how can I get the properties of a workbook without making the Excel application or the workbook visible. Then obviously close the Excel workbook and the Excel Object after I get the properties Any help with this would be appreciated Here is some code I am using. I need help getting it to work. When I run it, I get an error message "Run-time error '-2147417851 (80010105)' Method 'Open' of object 'Workbooks' failed" I am in a form in Acc...

Custom Error Bars in Excel 2007
When choosing the option to add a custom error bar in Exel 2007 and trying to set the range to a worksheet that does not contain the chart I receive an error in Excel that closes down Excel. The steps I perform are: 1. Click on a bar in my chart 2. Click Layout 3. Click the drop down for error bars 4. Click more error bar options... 5. Choose custom and click specify value. 6. Click to set the range where this is then displayed ={1}. As soon as I change to the worksheet to set the range Excel errors out and shuts down. I have recreated this on more than one machine and the result is t...

Number Records by group in Report
I would like to number or rank records in a report by grouping. The ranking or numbering would be based on the lowest time. I have a field of [HandleTime] in "nn:ss". The groups would be based off a department. Any Ideas. I have looked out numbering them in a query, but that does not help for the groupings. Thanks - David Add a text box to the Detail section, and give it these properties: Control Source =1 Running Sum Over Group This assumes the Sorting And Grouping box is sorting by the times within the group, and you don't have to show 2 identical times as...

19 Digits in a Cell
It has been necessary to put 19 digits in a cell but the last four revert to Zeros (0000) I have attempted various formats of the cell but of no availe, Why? and Help please. :confused: Bri -- bri ------------------------------------------------------------------------ bri's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27704 View this thread: http://www.excelforum.com/showthread.php?threadid=489539 Preformat the cell as text (format|cells|number tab|Text) Or start your entry with an apostrophe: '1234123412341234123 bri wrote: > > It has been...

Excel Invoice
I have an excel invoice that had macros from excel 4.0 when I open the invoice it ask me to enable or disable the macros I hit enable....I need to edit the cells and change some of the formulas around to get diffent figures...but i get an error that says the shaded cells are automatically calculated by excel therefore I cannot edit them I have unlocked the cells and ranges as well as have permission to the invoice but nothing seems to work....Do I need to have access to the workbook if so wouldnt that workbook be on my system so I could do this...??? I got a copy of this template from ...

Calculating the number of months
I have a column for start date and end date. I would like a formular that tells me how many months it is including the start month and end month. ex. Start date: 11/1/2009 end date: 4/1/2010 this would be 6 months if you include November and April. Thanks in advance hi assuming that start date is in A1 and end date is in b1.... =DATEDIF(A1,B1,"m")+1 regards FSt1 "Philangr8" wrote: > I have a column for start date and end date. I would like a formular that > tells me how many months it is including the start month and end month. > > e...

adding numbers in a column with dates
I have a column with dates and numbers. I want to add up the numbers at the bottom of the column. For example: A1 11/15 A2 33 A3 11/20 A4 45 A5 11/24 A6 66 etc... I just want to add A2, A4, A6, etc.... Is there an easy way to do this? I know that I can single out by =SUM(A2,A4,A6), but there are a lot more to add in the column and I'll be doing it for each month. This way would be so tedious and time consuming. Can anyone help? Are the other values text ('11/15) or really numbers? If they're really text, you could use: =sum(a1:a10) Text values will be...

Formula for checking duplicate numbers
I have to record apt unit numbers in separate columns. I could up to 20 or 30 columns. My problem is apt unit numbers being entered multiple times. Is there something that can check for duplicate numbers and let me know? Thanks If the columns are in a contiguous block then it's fairly easy. If the columns are scattered around the sheet then it could be complicated. -- Biff Microsoft Excel MVP "Ron" <sid2059@gmail.com> wrote in message news:536C7D2B-1E76-4D18-AEF2-032FF55C894E@microsoft.com... >I have to record apt unit numbers in separate columns...

Problem with Importing .csv into Excel
Hi, I have a csv file with a number of words (probably 1000-2000) separated by the "|" character. I wanted to import this file into Excel (Office 2003 Excel), so that I could get each word on a separate line so I could paste them into a blank text document. The problem is, when I go to import the .csv file, Excel imports each word into a column heading, & seeing as how it is limited to 256 columns, my file is not fully imported. I see that Excel has a 65000 row limit, which is more then enough, but I cannot see a way to get Excel to import each word as a separate row, rather tha...

Convert *.BMP to Excel shape
Hi there, I have *.BMP file with freeform outline. Does anybody know any method to convert this *.BMP to Excel AutoShape? Of course I can Insert Picture from this file and then make new Freeform shape using my *.BMP as a template but I need to make about 50 shapes: Thank you. ...

Excel Solver Issue
I am trying to use the excel solver and am running into some issues. The solver is giving me results that I know are wrong and I cannot get it to give me the correct results. I am using it to calculate a linearity which is based off of the STDEV and AVERAGE of a group of numbers. The formula I am using is: =TRUNC(SQRT(SUM(($B$2-AVERAGE($B$2:$B$6))^2,($B$3-AVERAGE($B$2:$B$6))^2,($B$4-AVERAGE($B$2:$B$6))^2,($B$5-AVERAGE($B$2:$B$6))^2,($B$6-AVERAGE($B$2:$B$6))^2)/4),0)/AVERAGE($B$2:$B$6) This is basically the longhand version of STDEV/AVERAGE which I have found gives the actual number I am lo...

Problems with formating and formulas in Excel 2002
I am having problems with the sum formula in Excel 2002. I take a straight copy of data in an acess database (not an export) and paste it into an excel spreadsheet. I can not seem to format the cells nor can I use the Sum formula to calculate. If I manually re-type all the numeric value these problems seem to disappear. The problems seem to have started when Access XP was installed onto the PC but niether removing Access no re- installing the PC has worked to correct this problem. Has anyone else seen something similar? Hi It sounds to me like the cells are coming in as text, rathe...

Excel XP crashes when printing, etc.
When I use Excel, there are some functions that cause the application to crash. These include printing, changing font attributes and other common operations. The application error info is: AppName: excel.exe AppVer: 10.0.6501.0 ModName: ntdll.dll ModVer: 5.1.2600.1217 Offset: 00033905 I have tried repairing my installation as well as deleting the entire Office XP install and doing it again from scratch. I am running all current WinXP and Office XP patches from the Windows Update and Office Update sites. Any Ideas? Sounds like your print driver is bad. You might check at the man...

Entering date QUICKLY
Do you know how to enter dates (in non US format) without using the / character? Eg 091204 instead of 09/12/04. I have seen it done, but don't know how. Please post to one group only. See my answer in other group -- Kind Regards, Niek Otten Microsoft MVP - Excel "DOMINIC JOSLIN" <patmag1@iprimus.com.au> wrote in message news:41b9678f$1_1@news.iprimus.com.au... > Do you know how to enter dates (in non US format) without using the / > character? Eg 091204 instead of 09/12/04. I have seen it done, but don't > know how. > Hi use the following...

excel formula to convert server time in seconds
I extracted data from my server. It keeps a log of time like this 1069255091 (this morning) I converted the data into excel but now I need this cell value to b the actual date and time. Any one have the easy answer ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com One way =DATE(1970,1,1)+F1/86400 format result as mm/dd/yy hh:mm:ss -- Regards, Peo Sjoblom "frogman1001" <frogman1001.x5hub@excelforum-nospam.com> wrote in message news:frogman1001.x5hub...

Excel charts #12
I am trying to format the individual labels on the category axis on an Excel chart. I want every other label in a different color and bold. But the entire category axis is formatted. Does anyone have a suggestion? The category axis is years - 1990 through 2005. I want the odd years to be bold and a different color than the even years. When Excel doesn't let me do what I want with a chart axis, I draw my own: http://peltiertech.com/Excel/Charts/ArbitraryAxis.html Use this technique to make two different axes, one for even and one for odd, and hide the default axis Excel draw...