Formula problems: Some numbers don't add up right.

[Guest]

I am working with an accounting worksheet and using the following formula:
=If(D12-D13 = 0, "Balanced", D12-D13)

D12 is a subtotal for all entries on the page and D13 is a total from
another cell. The problem I am having is that most of the time the function
works properly, and it will give me the "Balanced" answer that I am looking
for. However, when I change some of the numbers in the list (which changes
the subtotal) and I change D13 to match it, instead of getting "Balanced" I
get a difference of 0.00. If the difference is 0, then why is it not showing
"Balanced"? For example, 15.50+20.00 = 35.50 for a subtotal for D12. When I
put 35.50 in D13 it says "Balanced". However, when I put $57.96+4237.99 =
4295.95, but when I put 4295.95 in D13, I just get "0.00", the difference
between the two. There are no extra decimal points or spaces or anything like
that in any of the figures. Sorry to babble on and on, but has anyone else
encountered this before or have any suggestions? Thanks!

 

[rsenn]

It's probably rounding.

The real number in Excel resulting from it's calculations may be
something like 0.00000000000000000001 which is displaying as zero.

Try something like

=If(D12-D13 = 0, "Balanced", Round(D12-D13),2)

 

[Guest]

Thank you, that did the trick!

 

[Guest]

You already have a workaround; here is the explanation. Excel (and almost
all computer software) does math in binary. Most decimal fractions are
non-terminating binary fractions, and can only be approximated. The effects
of these approximations have accumulated sufficiently to impact your
comparison. The only 2-digit decimal fractions that do not have to be
approximated are .00, .25, .50, and .75.

Your specific example does not exhibit the problem that you describe,
therefore at least one of 57.96 4237.99 in your example had to have been
calculated from other numbers rather than being entered directly. If you
want to learn more about the actual approximations that Excel (and almost all
other computer software) uses, you might find the functions at
http://groups.google.com/group/microsoft.public.excel/msg/b106871cf92f8465
to be useful.

In your position, you might find it useful to modify rsenn's workaround
slightly to
=If(ROUND(D12-D13,2)= 0, "Balanced", ROUND(D12-D13,2))

Jerry