problem with minus in Excel 2003
 

Hello!

I have two numbers in two locations :

A1
209951201,868000000000000000000000
A2
109951202,025000000000000000000000

when i create formula - =A1-A2

answer is = -0,157000005245209000000000

but in excel 2000 i have answer - -0,157000000000000000000000

Why excel 2003 show bad answer????

Pls Help

problem with minus in Excel 2003
 

I get the same answer -0.157000005245209000000000 with all versions of Excel
from Excel97 to Excel 2007.

The reason why the answer is not -0.157 is because the arithmetic is done
using binary double precision floating point arithmetic using the IEEE
standard.

"m.jeziorek" wrote in message
...
Hello!

I have two numbers in two locations :

A1
209951201,868000000000000000000000
A2
109951202,025000000000000000000000

when i create formula - =A1-A2

answer is = -0,157000005245209000000000

but in excel 2000 i have answer - -0,157000000000000000000000

Why excel 2003 show bad answer????

Pls Help

problem with minus in Excel 2003
 

In excel 2000 i have correct answer


How can i fix it?

"Charles Williams" wrote:

I get the same answer -0.157000005245209000000000 with all versions of Excel
from Excel97 to Excel 2007.

The reason why the answer is not -0.157 is because the arithmetic is done
using binary double precision floating point arithmetic using the IEEE
standard.

"m.jeziorek" wrote in message
...
Hello!

I have two numbers in two locations :

A1
209951201,868000000000000000000000
A2
109951202,025000000000000000000000

when i create formula - =A1-A2

answer is = -0,157000005245209000000000

but in excel 2000 i have answer - -0,157000000000000000000000

Why excel 2003 show bad answer????

Pls Help

problem with minus in Excel 2003
 

I do not know why your Excel 2000 does not get the same answer as my Excel
2000 and all other Excel versions, I cannot duplicate your results.

You can fix it by changing your formula to
=Round(A1-A2,3)

Charles
___________________________________
London Excel Users Conference April 1-2
The Excel Calculation Site
http://www.decisionmodels.com

"m.jeziorek" wrote in message
...
In excel 2000 i have correct answer


How can i fix it?

"Charles Williams" wrote:

I get the same answer -0.157000005245209000000000 with all versions of
Excel
from Excel97 to Excel 2007.

The reason why the answer is not -0.157 is because the arithmetic is done
using binary double precision floating point arithmetic using the IEEE
standard.

"m.jeziorek" wrote in message
...
Hello!

I have two numbers in two locations :

A1
209951201,868000000000000000000000
A2
109951202,025000000000000000000000

when i create formula - =A1-A2

answer is = -0,157000005245209000000000

but in excel 2000 i have answer - -0,157000000000000000000000

Why excel 2003 show bad answer????

Pls Help

problem with minus in Excel 2003
 

All versions of Excel give -0.157000005245209 as the answer for
=209951201.868-209951202.025
(I have assumed the obvious typo in your original post) For you to not get
the trailing ...5245209 in Excel 2000 indicates that rather than entering
these values, you calculated at least one of them in a formula, and that
formula value is not the same as the corresponding 2003 value beyond the 15th
significant figure.

As documented, Excel will display no more than 15 significant figures. When
you request more, you get a displayed value padded with trailing zeros that
have no relationship to the actual value stored. You can use the VBA
functions at
http://groups.google.com/group/micro...fb95785d1eaff5
to see more figures.

The arithmetic leading to the Excel 2003 answer is actually quite correct,
though potentially surprising. Almost all computer software and hardware do
binary math with finite precision. Most terminating decimal fractions
(including .828 and .025) are non-terminating binary fractions that can only
be approximated in binary (just as 1/3 can only be approximated as a decimal
fraction). The result is that you got the exact answer to an approximation
to your intended problem.

In general, you should realize that floating point numbers often have values
other than what you intended beyond the 15th significant figure. You cant
see these approximations directly (15 digit display limit) but they may be
revealed as a result of subtraction of numbers that agree at the first few
figures. You are the only person who knows what calculations you will do,
and therefore you are the only person who can determine what adjustments will
protect you from these unavoidable consequences of finite precision.

You subtracted numbers that agreed to 8 figures, so the result may have no
more than 7 (15-8) figures that agree with your intended problem, as opposed
to the approximate problem that finite precision forced Excel to use. For
simple addition and subtraction, rounding the result to the appropriate
figures will move finite precision approximations back out beyond the 15
digit display limit, without violence to the calculation.

Jerry

"m.jeziorek" wrote:

Hello!

I have two numbers in two locations :

A1
209951201,868000000000000000000000
A2
109951202,025000000000000000000000

when i create formula - =A1-A2

answer is = -0,157000005245209000000000

but in excel 2000 i have answer - -0,157000000000000000000000

Why excel 2003 show bad answer????

Pls Help