Stop Excel rounding whole numbers

[Guest]

Hi

I am using Excel to track computer serial numbers. In some instances the
number is rounded down to the nearest 10 whole number. How do I stop Excel
from doing this? Examples are:

4171053200000698 rounds to 4171053200000690
4171053200000693 rounds to 4171053200000690

I have changed the format of the cell to many different types of number
formats with and with out decimals before and after entering the number and
it still does this.

 

[Alan]

Hi

I am using Excel to track computer serial numbers. In some
instances the number is rounded down to the nearest 10 whole number.
How do I stop Excel from doing this? Examples are:

4171053200000698 rounds to 4171053200000690
4171053200000693 rounds to 4171053200000690

I have changed the format of the cell to many different types of
number formats with and with out decimals before and after entering
the number and it still does this.

Hi,

That is as per Excel's specifications (see documentation) - the max
number of significant figures for a numric value is 15.

If you want it to keep a longer 'number' then make it a text string.

HTH,

Alan.

 

[Jerry W. Lewis]

While there is no way to make Excel directly display a 16th digit (see
Help for "Excel specifications and limits" subtopic "Calculation
specifications"), most 16 digit whole numbers (<=2^53=9007199254740992)
can be exactly represented (Excel's internal number representation is
IEEE double precision).

If you need to calculate with them, will not have intermediate results

2^53, and will have a final result of 15 figures or less, you might use

=4171053200000690+8 displays as 4171053200000700
=4171053200000690+3 displays as 4171053200000690
and the difference is 5, as it should be.

Formulas are the only way to access the full precision of IEEE double
precision. Otherwise, as you have noted, Excel will truncate to 15
digits before conversion to binary.

I have posted VBA code
http://groups.google.com/group/microsoft.public.excel/msg/6efb95785d1eaff5
that can be used to determine the exact numeric value in a cell.

Jerry

 

[KJ]

While there is no way to make Excel directly display a 16th digit (see
Help for "Excel specifications and limits" subtopic "Calculation
specifications"), most 16 digit whole numbers
(<=2^53=9007199254740992) can be exactly represented (Excel's internal
number representation is IEEE double precision).

If you need to calculate with them, will not have intermediate results
=4171053200000690+8 displays as 4171053200000700
=4171053200000690+3 displays as 4171053200000690
and the difference is 5, as it should be.

Formulas are the only way to access the full precision of IEEE double
precision. Otherwise, as you have noted, Excel will truncate to 15
digits before conversion to binary.

I have posted VBA code
http://groups.google.com/group/microsoft.public.excel/msg/6efb95785d1eaff5

that can be used to determine the exact numeric value in a cell.

Jerry

Why not just split into two columns with the primary prefix in one
column and the postfix in the other. Then add a third column with
either concatenate or "&" to join the contents of the previous two columns.

I suppose you could also format those as text.....but that can lead to
other problems depending on your use.

 

[Ken Wright]

They are serial numbers and presumably require no calculation of any sort.
Format the cells as text before entering the data and you will be fine.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Guest]

Legend...simple but it works well.
Thank you very much.

 

[Guest]

Learned something simple! thanks!

 

[Guest]

I have the same problem. I have done EVERYTHING that Excel Help suggests for
this problem (increase decimals/decrease decimals, expand column) but it
continues to round.

ANY OTHER SUGGESTIONS!!!!!?

 

[garfield-n-odie [MVP]]

See http://support.microsoft.com/?kbid=269370 "Last digits are changed
to zeroes when you type long numbers in cells of Excel".

 

[Simon Murphy]

Try Text format and/or prepending an apostrophe

cheers
Simon
Blog: www.smurfonspreadsheets.net

 

[sshek]

Hi I am trying to keep a list of ICCID or SIM card numbers for cell phones. Each SIM has a 20 digit number. Each time I copy that number into excel it rounds it into
8.9014E+19 or 89014104276859800000 even after I have formatted the cell to text. Is there any other trick to prevent it from converting the text and rounding it and keep it as text.