• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copying the numbers from Google spread sheets to excel file

Dear Friends,

Could you please help me out to copy the the exact numbers from Google spread sheet to regular excel work book without getting below text.

Actual Data in Google Spread sheet is

4629520371911009 when coping the same in another excel sheet resulting as
4.62952E+15. Hence, kindly help me out for the same.

Your kind support in this regard really helps a lot for my day to day work.

Thanks in advance,
Kumar
 
You are getting the output as is because you have exceeded Excels numeric display.


Whenever you work with large files in Excel, so many worksheets with countless formulas, you have probably wondered, how does Excel do it so fast… Well if it wasn’t for IEEE 754, it would take an eternity. But this cutting of corners or rather of numbers also has a downside. But first things first. What is IEEE 754?

“Microsoft Excel was designed around the IEEE 754 specification with respect to storing and calculating floating-point numbers. IEEE is the Institute of Electrical and Electronics Engineers, an international body that, among other things, determines standards for computer software and hardware. The 754 specification is a very widely adopted specification that describes how floating-point numbers should be stored in a binary computer. It is popular because it allows floating-point numbers to be stored in a reasonable amount of space and calculations to occur relatively quickly. The 754 standard is used in the floating-point units and numeric data processors of nearly all of today’s PC-based microprocessors that implement floating-point math, including the Intel, Motorola, Sun, and MIPS processors.” (Direct quote from http://support.microsoft.com/kb/78113)

You will notice this standard in many ways in Excel, but the main is, that if you write an integer with more than 15 digits (which is quite feasible), excel will transform all integers starting with the 16th to zero.

So when you put 1234567890123456789 in a cell, you get 1234567890123450000. The same goes for 1234567890.123456789 that would give 1234567890.123450000! This is by itself quite a drawback, but it doesn’t end there, this limitation impacts all parts of Excel, including calculations. Below is a very simple calculation…
051914_1314_15digitlimi2.png


Adding the three numbers in A1, A2 and A3 should result in 0, but you get something else entirely different. This is due to another limitation that is a side effect of the IEEE 754 or more precisely a side effect of something that excel did not adopt from IEEE 754 and that is the “negative zero”. So when excel should give something like -0.x as a side result or as the end result, you are in trouble. With this in mind it can be said, that Excel doesn’t really calculate correctly, and if we would like to continue our journey to eternal happiness in Excel, this is good to know!

Is there a way around this
The short answer is NO. The long answer is, you can store longer numbers text (so begin writing in Excel with an apostrophe), and you will see more than 15 integers, but if you will want to convert them back to numbers and do calculations with them, you will again only work with 15 integers!

The only way to make Excel more precise, is by using an Add-In.
 
  • Like
Reactions: Xiq
Back
Top