Computer Hope
Software => Computer software => Topic started by: aarkrao on May 03, 2011, 09:28:38 AM
-
A friend of mine has posed the following puzzle and I could not find an answer to it. Hence I am posting it here :
In excel, when you enter a series of digits exceeding 16 digits , you can not see them. If you keep a general format, a,bcde+Y format appears (where Y is the number of digits entered). If we, on the other hand, format it to "number" format, all the numbers after first 15 digits are coverted to '0'.
(Both in MS excel 2003 and 2010).
Why and How to overcome this problem?
I have faith that , as usual, learned friends in this forum will guide me.
Thanks and Regards
-
Like most computer apps (COBOL is an exception), Excel uses the IEEE convention for storing decimal numbers as binary numbers. This results in a limitation on the number of digits that can be stored - 15. But are you working with genuine numbers? Things like ISBN, telephone, etc numbers are not really numbers in the sense that we never perform arithmetic on them. In this case you have two workarounds: a) before entering the value format the cell as text OR b) preface the value with single quote (apostrophe) - this will neither display nor print (visible only in the formula bar).
Below:
into A1 I typed 1234567890123456789012345
into A2 I typed '1234567890123456789012345
(http://i124.photobucket.com/albums/p29/badoit/excel-big-number.jpg)
-
Thank you, Sir.