I’m using MS Excel 2000 v9.0 and I’m trying to calculate very large numbers. However, when I get into the 15 digit range the program starts to round the last 2 digits. I’d like to know why.

Try this:
Format cells in column A to ‘Number’ with zero decimal places, and make it wide enough to fit about 16 digits.
In cell A1 type a 1
In cell A2 type a 2
In cell A3 type this formula: =A2+A1, you should get a 3
Now copy that formula all the way to cell A75
You’ll see that the last digit in cell A71 is a 4, and the last digit in cell A72 is a 3. This should make the last digit in cell A73 a 7, but on my spreadsheet it is a zero. It has rounded 1,304,969,544,928,657 to 1,304,969,544,928,660. Why??????

http://precisioncalc.com/What%20is%20xlPrecision.html

Check under the heading "Not an EXCEL problem".

Interestingly, A74-A73 does give you exactly A72, so it appears to be a limitation in the display, but not actually the number in the memory.

Kendor:

Please read:

XL: Problems with Statistical Functions and Large Number.

Microsoft Knowledge Base Article Q158071.

This article explains that any digit above 15 will be rounded off.

Don

this is interesting.
you will have to balance your checkbook w/out excel.

It's the same reason you can only measure 1/16th of one inch on most rulers.

A 15 digit number is most likely declared as a 'double', therefore is limited in its precision.

Christ/Kendor:

Be reasonable. 2 digits out of 15 digits is a very very small error.

After all you are talking about a number that is 100,000 Trillion.

The first two digits to the left of the decimal point, in this large a number is insignificant.

Don

It all depends on the mathematics. 15-digit precision is not very precise to be honest with you. Sure, it's more than enough precision to balance a checkbook and perform basic accounting calculations and such.

[QUOTE]Originally posted by Chris F:
It all depends on the mathematics. 15-digit precision is not very precise to be honest with you.

Don' Reply:

Your point is NOT at all undertstandable!

Sure, it's more than enough precision to balance a checkbook and perform basic accounting calculations and such.[

Don's Reply:

Then, what is your point of reasoning?

Don

I thought Chris's point was fairly clear. While double precision is fine for checkbooks (unless you have 100 trillion or more dollars in it), it can be a problem in other circumstances.

One of my co-workers has run into this problem in his data-workup, and obviously Kendor was calculating large numbers for some purpose.

Kendor - If you need to calculate with greater precision, there are add-ins available. I have no idea how well any of them work, though.

2 add-ins about 3/4 of the way down:
http://www.mathtools.net/Excel/Mathematics/

first add-in:
http://www.bizoffice.com/soft_msoffice_excel.html

others can be found by typing:
        multiprecision excel
                or
        multi precision excel
into a search engine.

My point of reasoning is that Excel is not limited by the types of tasks that can be performed within it. Excel is not just used to balance checkbooks, or perform basic accounting methods/calculations.

I, for one, would have used Excel extensively in Analytical Chemistry. Electron Capture Dissociation and Dissociation Constants are - for instance, upwards of 50 to 60 digits in precision. Excel is a very good tool, but I was left completely out in the woods.

My solution? I used Python, a high-level programming language. I was able to control what variable types I was using, and have program flow and control.

That, Don, is my reasoning.

I like how this thread just... died.

I'm with you Chris, Multiple percision arithmitic is an interisting topic.

Why would anyone want to calculate Pi to a million places. Not to many practical applications but its been done. ( Why - 'Cause it's there.)

Methos Wrote,
"Interestingly, A74-A73 does give you exactly A72, so it appears to be a limitation in the display, but not actually the number in the memory."

Microsoft Works SS does the same thing.
"Very interesting"

Mike b. (AKA Byter)