PDA

View Full Version : large number in excel

bpt2
29-07-2006, 10:10 PM
What's the biggest number that I can calculate in excel. When I tried 111,111,111^2 I got 12,345,678,987,654,300 instead of 12,345,678,987,654,321.

Hhel
29-07-2006, 10:48 PM
bpt2

For what it's worth, I tried the same in Openoffice 2.0 under Xandros (Linux) and got the same answer as you did. It seems as if it is rounding the answer over those last three figures.

Jim

Parry
30-07-2006, 10:30 AM
In Excel Help type in Excel specifications and limits, then look under calculation specifications. The biggest number is 9.99999999999999E+307.

regards,
Graham

andrew93
30-07-2006, 12:14 PM
Hi Graham
I believe the original poster was referring to the accuracy of numbers in excess of 15 digits - most numbers greater than 10^15 are incorrect given the 16th and greater digits (counting from the left) are rounded to zero. That said, I don't think it is an Excel issue - I have seen the same thing with the Windows calculator. That's why I wrote a programme to correctly handle numbers > 10^15.
Cheers, Andrew

Parry
30-07-2006, 07:36 PM
{snip}
I believe the original poster was referring to the accuracy of numbers in excess of 15 digits

Ah I see, thanks Andrew. Its all to do with floating point precision and is a problem with computers in general it seems. For some dry reading see http://support.microsoft.com/kb/78113/ :D

regards,
Graham

bpt2
30-07-2006, 09:21 PM
Thanks for that

Graham L
31-07-2006, 05:52 PM
Of course it's only a problem if you have a need for that precision. I challenge you to measure anything to that precision in the real world.

Floating point calculations are always a trap for people who trust computer output.

Calculate 10/3 and multiply the answer by 3. For extra fun, compare the final result with 10.000 . :D

There's a whole branch of mathematics devoted to "Numerical Methods". A Google search for "Ieee 754" will find lots about the major standard used for computing.

If you want "exact" values, there are "infinite precision" systems, but they are often slow. Matlab will do it.