Excel Bug?

[tmwsiy]

My VP of Finance came to me with this bug. I admit that the if statement can
be written much better and there are several other ways to work around this
problem. But, her logic is correct and should work but it doesn't. The
weird thing is that she has been using this formula for years without any
issues and it appears to work will all other numbers?

Here is the A1:B2 range:
58751.04 58574.36
1906.66 2083.34

If you sum A you get 60,657.7 and the sum of B is equivalent. However the
following function displays error: =IF(SUM(A1:A2) - SUM(B1:B2) =
0,A1,"Error").
If you change the numbers in A1:B2 but keep the sums equivalent then the
function does not display an error?

This has to be a bug.

 

[Dwight Specht]

t:

Double check on small thing: look directly at the cell values in the
formula bar, or, alternatively, set the decimals displayed out to a larger
number. The formula should work, so I think you may have some numbers with
more than 2 decimals in the value hence there exists a very minor difference
in amounts.

Dwight

 

[Ron Rosenfeld]

My VP of Finance came to me with this bug. I admit that the if statement can
be written much better and there are several other ways to work around this
problem. But, her logic is correct and should work but it doesn't. The
weird thing is that she has been using this formula for years without any
issues and it appears to work will all other numbers?

Here is the A1:B2 range:
58751.04 58574.36
1906.66 2083.34

If you sum A you get 60,657.7 and the sum of B is equivalent. However the
following function displays error: =IF(SUM(A1:A2) - SUM(B1:B2) =
0,A1,"Error").
If you change the numbers in A1:B2 but keep the sums equivalent then the
function does not display an error?

This has to be a bug.

This gets brought up here at least weekly.

Search for rounding errors at support.microsoft.com

You can fix the problem by rounding to some desired amount of precision.

For example,

=IF(ROUND(SUM(A1:A2)-SUM(B1:B2),5)=0,A1,"error")

or, even simpler, with your values:

=IF(SUM(A1:A2)=SUM(B1:B2),A1,"error")

But the rounding method should work with all values, and also will take care of
issues when the contents being compared are the results of formulas.

--ron

 

[tmwsiy]

Double check on small thing: look directly at the cell values in the
formula bar, or, alternatively, set the decimals displayed out to a larger
number. The formula should work, so I think you may have some numbers with
more than 2 decimals in the value hence there exists a very minor difference
in amounts.

Dwight

I did both of those Dwight and it showed only two decimals. For testing I
typed the number in directly with only two decimal places with the same
result, but only with values in my orginal post. If you change the number to
any other number the function works?

 

[Bob I]

It's the binary representation of those particular numbers that is the
cause. IF you round the "SUMS" to 2 decimal places before the compare,
the issue is moot. This has been so for many years.

 

[tmwsiy]

You can fix the problem by rounding to some desired amount of precision.

This works Ron and does solve the problem. My question now is why does
taking the difference of two sums with two decimals increase its precision,
and why only in this case?

=IF(SUM(58751.04,1906.66)-SUM(58574.36,2083.34)=0,"T","f") is false
=IF(SUM(58751.03,1906.66)-SUM(58574.35,2083.34)=0,"T","f") is true
=IF(SUM(0.04,0.66)-SUM(0.36,0.34)=0,"T","f") is false
=IF(SUM(0.66,0.01)-SUM(0.65,0.02)=0,"T","f") is true

 

[tmwsiy]

It's the binary representation of those particular numbers that is the

cause.

Perfect Bob I thank you!

..66 is a repeating decimal and is not finite when converted to binary so it
rounded to -2.8E-17 when it’s stored. This is why the problem seems
intermittent.

http://support.microsoft.com/kb/78113

 

[Ron Rosenfeld]

This works Ron and does solve the problem. My question now is why does
taking the difference of two sums with two decimals increase its precision,
and why only in this case?

=IF(SUM(58751.04,1906.66)-SUM(58574.36,2083.34)=0,"T","f") is false
=IF(SUM(58751.03,1906.66)-SUM(58574.35,2083.34)=0,"T","f") is true
=IF(SUM(0.04,0.66)-SUM(0.36,0.34)=0,"T","f") is false
=IF(SUM(0.66,0.01)-SUM(0.65,0.02)=0,"T","f") is true

It has to do with binary representation of decimal numbers. See that MSKB
article on "rounding errors" I mentioned earlier.
--ron

 

[Bob I]

You're most welcome, have a great day.

Perfect Bob I thank you!

.66 is a repeating decimal and is not finite when converted to binary so it
rounded to -2.8E-17 when it’s stored. This is why the problem seems
intermittent.

http://support.microsoft.com/kb/78113

 

[Jerry W. Lewis]

As Ron and Bob have already noted, this is a known and expected phenomenon
related to the fact that your decimal fractions (indeed most decimal
fractions) must be approximated since they have no exact finite binary
representation (just as 1/3 must be approximated as a decimal fraction).

A few of points that may aid your comfort level with Excel and your
intuition in this matter:

Excel (along with almost all numerical software) represents numbers per the
IEEE 754 standard (which is designed to minimize the accumulation of rounding
errors) for double precision, and appears to use the processor's basic math
routines instead of reinventing the wheel. Thus the results in this case
(and all others that I am aware of) are comparable to what you would get from
any other IEEE double precision software package, and are completely
predictable according to the rules of that standard.

Binary conversion coupled with the limits of double precision are such that
decimal figures beyond the 15th may not be what you would otherwise expect.
Excel will not directly show you anything beyond the 15th figure, but
subtraction may reveal what is there, as you have discovered. That 15-figure
rule of thumb can be used to predict the potential level of discrepancies due
to approximation without actually doing the binary conversions. However if
you want to see what is going on at the binary level, use the VBA functions I
posted at
http://groups.google.com/group/microsoft.public.excel/msg/6efb95785d1eaff5

For most accounting purposes (an obvious exception is accumulating compound
interest over multiple periods), you can round calculation results to the
smallest unit of currency (.01 in most instances I am aware of) without
violence to the math in order to assure that discrepancies due to
approximation remain beyond the 15th figure. Excel cannot do this for you
automatically, because Excel is a general purpose tool and it would decrease
the accuracy of many other types of calculations

=((58751.04+1906.66)-(58574.36+2083.34))
will show you the difference that your IF() function was seeing. This
result is numerically correct given the initial binary conversions to IEEE
double precision.
=(58751.04+1906.66)-(58574.36+2083.34)
returns zero because Excel arbitrarily zeros the result of final
subtractions where both numbers agree to 15 decimal digits
http://support.microsoft.com/kb/78113
Excel cannot do this other than for final subtractions, because doing so
would decrease the accuracy of some calculations. This fuzz factor is
related to why
=IF(SUM(A1:A2)=SUM(B1:B2),A1,"Error")
would work in this instance even though your original formula did not. It
is less common, but possible that the accumulation of discrepancies due
approximations could accumulate to the point that SUM(A1:A2)=SUM(B1:B2) would
be false despite MS’s fuzz factor, so rounding to an intelligently chosen
level is the better approach.

Here is a naive decimal example of what happened in your calculation.
Suppose a hypothetical computer does decimal math and carries only 4
significant figures. While it is mathematically true that
10/3 = 5/3 + 5/3 = 6/3 + 4/3
that is not numerically exact in finite precision since
1.666+1.666 = 2.332
2.000+1.333 = 3.333

Jerry