Re: Strange result in Excel 2000
- From: "Jerry W. Lewis" <post_a_reply@xxxxxxxxxxxxx>
- Date: Fri, 11 Nov 2005 23:57:23 -0500
While =($A$1+$B$1)-$C$1 returns zero, =(($A$1+$B$1)-$C$1) does not, and it is this later calculation that IF() is basing its decision on.
None of your numbers have exact binary representations, and hence must be approximated (just as 1/3 must be approximated in decimal). The (IEEE standard) binary approximations to the numbers involved in the final subtraction are
84.8999999999999914734871708787977695465087890625
-84.900000000000005684341886080801486968994140625
--------------------------------------------------
-0.0000000000000142108547152020037174224853515625
so Excel is exactly correct (given the approximations to the inputs) to report the answer for =(($A$1+$B$1)-$C$1) as -1.4210854715202E-14, and IF is correct to report that the result is not zero. And almost all other general purpose software will behave in the same manner. The real mystery is why =($A$1+$B$1)-$C$1 claims that the result is zero.
The key to that mystery is hidden in the knowledge base article (78113) that Bernard referenced. Where it says that "Excel 97, however, introduced an optimization that attempts to correct for this problem" [that finite precision binary math sometimes produces correct but unexpected results when subtracting numbers would be equal in decimal representations]. What they did is to assume that if a final subtraction involves two numbers that are equal to at least 15 decimal digits, then Excel will arbitrarily zero the result on the assumption that the non-zero difference is residue from binary approximations. This arbitrary zeroing does not occur if the subtraction is not the last operation (as when the last operation is surrounding parentheses, or within an IF function) since the required assumption may be wrong, in which case the arbitrary zeroing would reduce accuracy that introduced inaccuracy could be magnified by subsequent calculations.
IMHO this "optimization" was a mistake, since it fails to eliminate the situation it was aimed at, and instead makes Excel's math appear inconsistent even to those who do understand the binary issues.
Jerry
ibertram wrote:
Hello folks,
Have you encountered this in your Excel? I am using Excel 2000 SP3.
Let's say you have: Column A1: 57.16 Column B1: 27.74 Column C1: 84.90
In Column D1, you have this formula: =($A$1+$B$1)-$C$1. Result would be 0 But if you have this formula in column E1: =IF(($A$1+$B$1)-$C$1) = 0, "it is zero", "it is not zero"). Result would be "it is not zero". Strange huh?
It seems that it only happens to this set of number.
Do you have the same result in the other Excel version?
Thank you.
.
- References:
- Strange result in Excel 2000
- From: ibertram
- Strange result in Excel 2000
- Prev by Date: Re: convert min/km in km/h
- Next by Date: Re: lookup date, sum up total...
- Previous by thread: Re: Strange result in Excel 2000
- Next by thread: Avoiding Cells
- Index(es):
Relevant Pages
|
