16 digits

[Guest]

I need to type numbers that have 16 digits. Excel drops all digits after 15.

My format is set to Number with 0 decimal places, but I have also tried a
custom format with 16 zeros.

How can I make that 16th digit hold?

Jane

 

[Mark Lincoln]

Jane,

Excel only works up to 15 digits.

If you don't need to do calculations with those big numbers, then
format the cells as Text. Or enter the numbers with a leading
apostrophe, which Excel will treat as text:

'1234567890123456

 

[Dave O]

Excel can't handle numbers of that size without reverting to scientific
notation. Do you have to do math on these numbers, or are the account
numbers, or credit card numbers, or something that won't require math?
If yes, format the cells as text.

 

[Guest]

Thanks, Dave and Mark, for the quick response. I'm sorry to hear the answer,
but will cope with it!

 

[Guest]

I need to type numbers that have 16 digits. Excel drops all
digits after 15.

More accurately: it turns them into zero(!).

My format is set to Number with 0 decimal places, but I
have also tried a custom format with 16 zeros.
How can I make that 16th digit hold?

If you want the 16-digit string to be treated as a number,
I believe the answer is "you cannot".

If you simply want to enter a 16-digit string as text, you can
prefix it with a single quote (apostrophe). For example:
'12345678901234567890.

If you need to enter a number with more than 15 significant
digits, you can enter the first 15 digits using scientific notation.
For example: 123456789012345e2. But you still lose the
16th and more digits.

The problem is: internally, Excel uses a standard binary
representation of numbers that is accurate to only 15 decimal
digits. Excel could format a 16th digit, but it would be wrong
in most cases. Anything beyond that is pure fantasy.

 

[Dave O]

Jane, you're saying you do, in fact need to do math on those numbers?
Do you work at JPL? What are you doing?!

 

[Guest]

Actually someone else asked me today, and I didn't know there was a 15 digit
limitation, so I was curious on my own. I showed her how to format it to be
text, but she said she needed for them to be numbers.

She works for the FBI, and she couldn't tell me why she needs it, except
that they also need to get imported into Access later.

Thanks to all for the answers, and I hope I never need to calculate numbers
that big!

Jane

 

[Peo Sjoblom]

Gee! FBI is using Excel, no wonder they do so many mistakes. <bg>

--
Regards,

Peo Sjoblom

Portland, Oregon

 

[Guest]

she said she needed for them to be numbers.
She works for the FBI, and she couldn't tell me why she
needs it, except that they also need to get imported into
Access later.

She might look into some of the add-ins that do decimal
arithmetic instead of binary arithmetic. I am familiar with any
of them, so I cannot say that any might overcome this limitation.
But it is possible that one or more them does.

If you cannot find pointers to these add-ins using Google, you
might try searching this newsgroup and related ones. I am
certain that someone has posted a pointer to one or more such
add-ins in recent months (weeks?).

Of course, the add-ins might need to be approved for federal
use first. In my (limited) experience, that is a difficult process.
She would have to make the call based on her need.

 

[David McRitchie]

Yeah real big secret what 16 digits are for, and who is being
watched, bet they can't wait for all the extra rows and columns
in Excel 12.

 

[gregl]

Actually someone else asked me today, and I didn't know there was a 15 digit
limitation, so I was curious on my own. I showed her how to format it to be
text, but she said she needed for them to be numbers.

Hi Jane,

My add-in, xlPrecision 2.0.1, can do this. You can download the free
edition here:

http://PrecisionCalc.com

You'll find a general introduction and description there, too. Let me
know if you have any questions.


Regarding joeu2004's comment below about approval for Federal use, I
have no idea what that involves, but I'd certainly be very happy to
cooperate.


Thanks,

Greg Lovern
http://PrecisionCalc.com
Get Your Numbers Right