Why can't Excel add up? 1

[RayMunro]

I was surprised to learn that Excel cannot add up beyond a certain number. I ran a little table where A1 =1, B1=A1*2, A2 = B1, B2=A2*2 and ran this down to A65 This is, simply put 1x2x2x2 and so on. For the literary amongst you this is based on the old fable of putting a grain of rice on the first square of a chess board and doubling for all 64 squares.

The result surprised me because whe I got to A50 it started to "round up" 562949953421312 x2 =1125899906842620
now I know that 312 (the last 3 numbers) x2 = 624 not 620. This happened for all further calculations. Yet if I use Calculator it adds up perfectly. Any Ideas?

 

[Paul666]

Extract from MS KB article 65903

Microsoft Excel retains 15 significant digits. To display all 15 digits, you must use a number format (custom or built-in) other than General. The General number format displays up to 11 numeric characters, with the decimal point counting as a numeric character. Therefore, if the number contains a decimal point, Excel can display up to 10 significant digits, but if the number does not contain a decimal point, Excel can display up to 11 significant digits Adding more numbers to the left of the decimal point causes the number to appear in exponential notation.

This applies to all versions of Excel!

 

[RayMunro]

Sorry, I should have said I am using Number Format with no decimal places.

 

[Paul666]

It matters not. You still have the 15 digit limit.

 

[RayMunro]

Thanks for your help Paul666.
Does this mean I am stuck of is there a format I can use?

 

[Paul666]

I think you're stuck. It's actually a limitation with Excel.
However, I was thinking there may be a Visual Basic workaround where the calculation gets done in a macro then pasted back into the cell as text. It's a bit mess though. Might be worth you posting in the VBA forum?

Lets hope MS increase this limit in the next release!

 

[KenWright]

Excel will round all numbers to 15 significant figures. Anything over and
above this will be rounded to 0. If the data needs to be entered as you
have described, (eg for a credit card number) you need to precede the entry
with an apostrophe or format the cell as text before you enter the data.
You can still do calculations against a number entered as text BUT it will
only use 15 significant figures in the calculation, so that doesn't buy you
anything extra doing it that way.


A slightly edited (To generalise the response only), but very comprehensive
answer to a similar question was posted by Chip Pearson - Reproduced
below in it's entirety:-

----------------------------------------------------------------------------------------------------

As you have noticed Excel handles only 15 digits of precision.
The reason is that Excel, like many other computer programs, uses
the IEEE (Institute of Electrical and Electronic Engineers) Double
Precision Floating Point number format as the most accurate
representation of a number. You can read more about this at

http://www.cpearson.com/excel/rounding.htm

, but in an oversimplified
form, it stores numbers as

N = Integer + X*(1/2) + X*(1/4) + X*(1/8) + X*(1/16) +.....+
X*(1/2^51)

where each X is either 1 or 0. In binary format, there are 51
digits to the right of the decimal point. In decimal form, 2^51 is
about equal to 10^15, which is why you get approximately 15 digits
of precision.

Unless a fractional number can be expressed *exactly* as the sum of
1/2 + 1/4 + 1/8 + ... + 1/(2^51) it will be stored as an
approximation. This is not unique to computers. Using a finite
number of decimal places, you cannot accurately store the number
1/3. You can store it as an approximation, like 0.3 or 0.33 or
0.33333333333333 but at some point you're rounding the true value
1/3, and 0.33333333333...+0.33333333333...+0.33333333333... does
NOT equal 1. It equal 0.999999999999...... which is decidedly not
1.

This is a fact of life in computers and in the real world, and in
the realm in which the two coincide.

> But what about the rest of the decimal places, and how, if at all,
> can I achieve more precision?

You can *display* a number to as many decimal places as you want,
but anything past 15 is no man's land. Within Excel there is no
way to achieve additional precision. Errors in rounding can
compound, so that rounding error in one formula is compounded when
the rounded error is used by other formulas, which themselves
round.

Some computer programs use other representations of numbers, but
these programs trade performance and compatibility for precision.
Additional precision comes at the cost of performance and
compatibility with other programs. For example, a program that
stored numbers to 100 digits of precision would use a different
encoding scheme, and its data would not be compatible with the
majority of computer programs. The IEEE Double Precision standard
provides a universal format that is "good enough" for the vast
majority of uses. Not all, but most. For good reason, MS chose
years ago to use IEEE Doubles for Excel.

> Can you recommend a non-Excel app that offers higher precision?

Dedicated mathematical programs like Matlab and Mathamatica can
provide much greater precision, but those results aren't compatible
with most other computer programs.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC

http://www.cpearson.com

chip@cpearson.com


--------------------------------------------------------------------------------------------------------

For a calculator that will support more than 15 digits, Jerry W. Lewis has
given the following info and link:-


The decimal data type gives 28 figure if you don't need exponents and
don't mind VBA programing. I think the Windows calculator uses the same
data type.

A free quad precision (64 digit) calculator can be downloaded from

http://www.crbond.com/applications.htm

unless it has been updated, it does not support cut/copy/paste.

I think some extended precision routines using VBA and strings have been
published for Excel - search the Google archives.

In Maple, Mathematica, Matlab, Rexx, etc., you can specify the number of
output figures you want.


Jerry W. Lewis

----------------------------------------------------------------------------------------------------------

Hope this helps.

Regards
Ken............