I have three cells: A1, A2, A3. A1 and A2 appear to be the same number.
However, A1-A2 doesn't equal zero.

A1: 173,481.4421976590000000000
A2: 173,481.4421976590000000000
A3: 0.0000000004656612873

http://support.microsoft.com/kb/78113
http://support.microsoft.com/kb/214118
--
David Biddulph

"Steve" wrote in message
...
I have three cells: A1, A2, A3. A1 and A2 appear to be the same number.
However, A1-A2 doesn't equal zero.

A1: 173,481.4421976590000000000
A2: 173,481.4421976590000000000
A3: 0.0000000004656612873

In virtually all computer systems decimal numbers are approximate. Financial
systems go to great lengths to get around this limitation. Excel is good to
15 digits of accuaracy. After that the numbers can not be entirely trusted.
That being said how often do you need numbers that are accurate to 15 digits.
In your example (for simplicity lets assume that we are dealing in $) you
have a balance in excess of 173,000 and you are worried about less than 1 ten
millionth of a penny. The error is just not significant...
--
HTH...

Jim Thomlinson


"Steve" wrote:

I have three cells: A1, A2, A3. A1 and A2 appear to be the same number.
However, A1-A2 doesn't equal zero.

A1: 173,481.4421976590000000000
A2: 173,481.4421976590000000000
A3: 0.0000000004656612873

I don't care to be that accurate. I'm testing changes I made to our cost
model which is not written in Excel. I calculate the results in Excel and
compare them to the results I download out of the model. If they're the same
I know the model works as intended. If the result is off it may be rounding
or it may be a calculation somewhere that's causing it to be off by a small
amount. Running the model with other data may make the "off by" a lot larger.

"Jim Thomlinson" wrote:

In virtually all computer systems decimal numbers are approximate. Financial
systems go to great lengths to get around this limitation. Excel is good to
15 digits of accuaracy. After that the numbers can not be entirely trusted.
That being said how often do you need numbers that are accurate to 15 digits.
In your example (for simplicity lets assume that we are dealing in $) you
have a balance in excess of 173,000 and you are worried about less than 1 ten
millionth of a penny. The error is just not significant...
--
HTH...

Jim Thomlinson


"Steve" wrote:

I have three cells: A1, A2, A3. A1 and A2 appear to be the same number.
However, A1-A2 doesn't equal zero.

A1: 173,481.4421976590000000000
A2: 173,481.4421976590000000000
A3: 0.0000000004656612873

As documented in Help, Excel displays no more than 15 significant figures; if
you request more, Excel will simply pad the display with zeros. However, it
takes 17 significant figures to uniquely identify the value that Excel is
actually using; hence your inabilty to see a difference that you can detect
by subtraction.

=(A1&"")-(A2&"")
will calculate based only on what you can see (binary approximations to 15
figure numbers). This formula will return zero when the values agree to 15
figures, but will still have extraneous residue of binary approximations if
the values are nearly, but not quite equal; for example with
173,481.442197659 and 173,481.442197658, you will get 9.89530235528946E-10
for the difference instead of 1E-9.

Jerry

"Steve" wrote:

I don't care to be that accurate. I'm testing changes I made to our cost
model which is not written in Excel. I calculate the results in Excel and
compare them to the results I download out of the model. If they're the same
I know the model works as intended. If the result is off it may be rounding
or it may be a calculation somewhere that's causing it to be off by a small
amount. Running the model with other data may make the "off by" a lot larger.

"Jim Thomlinson" wrote:

In virtually all computer systems decimal numbers are approximate. Financial
systems go to great lengths to get around this limitation. Excel is good to
15 digits of accuaracy. After that the numbers can not be entirely trusted.
That being said how often do you need numbers that are accurate to 15 digits.
In your example (for simplicity lets assume that we are dealing in $) you
have a balance in excess of 173,000 and you are worried about less than 1 ten
millionth of a penny. The error is just not significant...
--
HTH...

Jim Thomlinson


"Steve" wrote:

I have three cells: A1, A2, A3. A1 and A2 appear to be the same number.
However, A1-A2 doesn't equal zero.

A1: 173,481.4421976590000000000
A2: 173,481.4421976590000000000
A3: 0.0000000004656612873