Function Bug ?

[ddelong]

I believe this is an excel bug in which I will do my best to explain.
Stick with me on this one.
I am using Excel 2000 v9.0.2720
I have a column (column C) of numbers like this:

C4 1,689,772.85
C5 (1,660,919.76)
C6 0.00
C7 28,853.09 =sum(C4:C6)
C8
C9
C10 28,853.09 =sum(C7:C9)
C11
C12 8,187.14
C13 13,257.13
C14 50,297.36 =sum(C12:C13)
C15 -363.80
C16 -3.46
C17
C18
C19 49,930.10 =sum(C14:C18)
C20
C21
C22 49,930.10 =sum(C19:C21)
C23
C24
C25
C26
C27
C28 49,930.10 =sum(C22:C27)
C29
C30
C31
C32 ERROR This is formula:
=if(C19=C28,0,"ERROR")

Now, as you can see C19 DOES EQUAL C28 and therefore 0 should print in
C32.
If I change C7 from the SUM of C4:C6 and simply type in 28853.09 (Which
is the same as the sum) then my formula on C32 works and the 0 prints in
C32
Even more strange.... If I leave C7 as =sum(c4:c6) but change the
numbers in C4 to C6 to 28854.09 and -1.00 respectively giving me the
same 28853.09 as before the C32 formula works again !
I can type in pretty much any combination of numbers in C4 and C6 as
long as the sum comes to 28853.09 and my C32 formula works BUT if I
type in the numbers shown above in C4 and C6 my formula fails yet the
sum is EXACTLY THE SAME !!!

Any help anyone can give on this is greatly appreciated.

Thanks.

 

[swatsp0p]

This is not a bug, but rather the way Excel handles numbers. If you us
the formula =ROUND(SUM(C4:C6),2) your problem should go away. You ma
need to 'round' other SUMs as well.

Does this work for you

 

[ddelong]

Thank you, Thank you ! It worked. I've been going bananas on this
one.

Can you tell me what it is about those numbers that I used that would
require me to round when I'm simply doing a sum ?

Thanks again !

 

[swatsp0p]

I'm not good at explaining that, but it is the way binary computers dea
with decimal numbers. It probably has your number as nnn.08999999
instead of .09.

That is the quick and simple explanation. Someone may want t
elaborate on this.

Good Luck!

As a follow up, I am curious about your post: you list the following:

C12 8,187.14
C13 13,257.13
C14 50,297.36 =sum(C12:C13)

when in fact, C12+C13=21,444.27 not 50,297.36 Is this a typo? Jus
wonderin

 

[ddelong]

Yes I had a typo in my sum, it should've been sum(c10:C13) good catc
!

Thank you for the explanation but it still doesn't explain why I coul
use other numbers to add up to 28853.09 and be fine or even simply typ
28853.09 in that field and not use the sum function.

Should I use the round function EVERY time I use the sum function s
that this can't happen ?

If someone could elaborate I would appreciate it

 

[KL]

Hi ddelong,

You may want to have a look at this:
http://support.microsoft.com/kb/78113/en-us

Regards,
KL

 

[JE McGimpsey]

See

http://mcgimpsey.com/excel/pennyoff.html

and

http://cpearson.com/excel/rounding.htm

 

[Ron Rosenfeld]

Yes I had a typo in my sum, it should've been sum(c10:C13) good catch
!

Thank you for the explanation but it still doesn't explain why I could
use other numbers to add up to 28853.09 and be fine or even simply type
28853.09 in that field and not use the sum function.

Should I use the round function EVERY time I use the sum function so
that this can't happen ?

If someone could elaborate I would appreciate it.

Jerry W Lewis writes about this frequently. And yes, if you are interested
that two separate SUMS equate exactly to zero, you will either have to use the
ROUND function, or use INTEGER math.

Here is one of Jerry's previous postings about a similar issue. The concept is
applicable to your problem:

===========================
the easiest way to think about these issues is to recognize that
Excel's documented limit of 15 decimal digit accuracy relates to the
accuracy of decimal/binary conversion. Thus your problem can be thought
of as
-30584.0100000000?????
30585.6000000000?????
----------------------
1.5900000000?????
which is consistent with your result of
1.59000000000015

The binary approximation to 30584.01 is 8406868654754365/274877906944
The binary approximation to 30585.6 is 4203652855313203/137438953472
The exact difference between these numbers is 437055872041/274877906944
which in decimal is 1.59000000000014551915228366851806640625, or
1.59000000000015 to 15 figures.

In keeping with Excel's documented limit of 15 decimal digit accuracy,
if you ask for more than 15 decimal digits, the trailing digits are
arbitrarily displayed as zero.

Jerry
===================================
--ron

 

[Jerry W. Lewis]

You can see the impact of accumulating binary approximations by copying
cell C14 and Edit|Paste Special|Values you will see that the value is
50297.3600000001, instead of 50297.36. The sum in C7 is not visibly
different from 28853.09, but =(C7-28853.09) shows that even here you
have a small difference from what you expected.

The issue is not the SUM function per se (the math is correct), but
unavoidable approximations to the inputs. When you do math with
approximate inputs, it should be no surprise that the result is only
approximate. Almost all decimal fractions have no exact binary
representation (just as 1/3 has no exact decimal representation). It is
also worth noting that this applies to almost all computer software;
Excel is representing numbers as specified in the IEEE standard.

The original question was why
=IF(C19=C28,0,"ERROR")
returns "ERROR" instead of 0. I cannot reproduce this result given the
formulas specified for C22 and C28, and the posted description that
C20:C21 and C23:C27 are blank (so that C28 is equivalent to =C22 and C22
is equivalent to =C19. Possibly one or the other of C22 and C28 was
hand enetered instead of calculated by the posted formulas? If C19:C28
were really as posted, what version of Excel was used?

Jerry