Entering large numbers into Excel

B

bl

I am trying to enter this number(110727309194833644) into a cell but Excel
changes the number once I press the Tab key and makes it this
number(1.0727E+17), I need to have the above number in the cell not this
other number. How can I make Excel put the above number in the cell and not
automatically change it?
 
M

Mike H

Hi,

You can't.

There is a limit of 15 digits precision in Excel (you can use more
digits if you treat the values as text). This is a direct consequence
of the way Excel stores numbers in binary format; as defined by IEEE,
and in common with many other applications.

Mike
 
M

Marcelo

hI,

Excel acepts the first 15 digits of the number the next ones will be
replaced by zeros,

for instance 12345678901234567890 will return 12345678901234500000

you can format is as text.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"bl" escreveu:
 
J

James Silverton

Marcelo wrote on Wed, 30 Jul 2008 12:56:00 -0700:
Excel acepts the first 15 digits of the number the next ones
will be replaced by zeros,
Click to expand...
for instance 12345678901234567890 will return
12345678901234500000
Click to expand...
you can format is as text.
Click to expand...
hth
Click to expand...
"bl" escreveu:
Click to expand...

I can't think of a reason why you'd want to enter numbers as large as
that in Excel given its limited precision but I suppose you could enter
the upper digits in one cell and the lower in the next column. You might
also have to define functions to deal with the numbers. Personally, if I
saw a need, I'd get my employer to buy me Mathematica or possibly,
Matlab.
--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not
 
D

Dave Peterson

Maybe you could enter the value as text.

Precede your entry with an apostrophe: '12341234123412341234
or
preformat the cell(s) as text, then do the data entry.
 
K

k_hersh

In response to "I can't think of a reason why you'd want to enter numbers as
large as
that in Excel given its limited precision but I suppose you could enter
the upper digits in one cell and the lower in the next column. You might
also have to define functions to deal with the numbers. Personally, if I
saw a need, I'd get my employer to buy me Mathematica or possibly,
Matlab."

I work for the Federal Government....more specifically, the area that
publishes the "Daily Debt to the Penny" figure. As of yesterday, we hit the
10 trillion mark, but are unable to publish the full figure. Now we're
scrambling around trying to come up with a quick solution because our numbers
have to be published on a deadline. The other software mentioned
(Mathematica or Matlab) are probably not an option because they're not
supported here at the Bureau. Entering the number in 2 different cells or as
text are also not options for us. We need a fix ASAP.

Someone from our office contacted Microsoft several years ago about this,
telling them that we needed the extra digits for certain functions and they
basically said, "Sorry 'bout your luck."

I'm hoping if enough people complain to Microsoft, that they'll eventually
do something about it. Some of our figures are published to the internet in
Excel format so pretty soon it won't just be a problem for a select few here
at the Bureau, but a problem for many.
 
D

David Biddulph

You can enter the large numbers as text, and if you want to do artithmetic
with the numbers you can define formulae (using LEFT/ MID/ RIGHT) to split
them into chunks which Excel can handle (as suggested here on a number of
occasions in in the past).
 
B

Bill Sharpe

k_hersh said:
I work for the Federal Government....more specifically, the area that
publishes the "Daily Debt to the Penny" figure. As of yesterday, we hit the
10 trillion mark, but are unable to publish the full figure. Now we're
scrambling around trying to come up with a quick solution because our numbers
have to be published on a deadline. The other software mentioned
(Mathematica or Matlab) are probably not an option because they're not
supported here at the Bureau. Entering the number in 2 different cells or as
text are also not options for us. We need a fix ASAP.
Click to expand...
Getting off topic here, but with the debt at 10 trillion dollars plus, I
would say calculating the number to the exact penny every day is an
exercise in futility. (The first word that popped to mind was
"stupidity", but let's leave it at futility)

Still off topic and on soapbox: Perhaps it's time to eliminate the penny
as a coin, since it costs more than one cent to produce each one.

Back to topic: Workarounds are available as others have suggested.

Bill
 
Top