software

I am working with an accounting worksheet and using the following formula:
=If(D12-D13 = 0, quot;Balancedquot;, 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 quot;Balancedquot; 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 quot;Balancedquot; I
get a difference of 0.00. If the difference is 0, then why is it not showing
quot;Balancedquot;? For example, 15.50 20.00 = 35.50 for a subtotal for D12. When I
put 35.50 in D13 it says quot;Balancedquot;. However, when I put $57.96 4237.99 =
4295.95, but when I put 4295.95 in D13, I just get quot;0.00quot;, 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!


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, quot;Balancedquot;, Round(D12-D13),2)--
rsenn
------------------------------------------------------------------------
rsenn's Profile: www.excelforum.com/member.php...oamp;userid=29050
View this thread: www.excelforum.com/showthread...hreadid=515132Thank you, that did the trick!
quot;rsennquot; wrote:

gt;
gt; It's probably rounding.
gt;
gt; The real number in Excel resulting from it's calculations may be
gt; something like 0.00000000000000000001 which is displaying as zero.
gt;
gt; Try something like
gt;
gt; =If(D12-D13 = 0, quot;Balancedquot;, Round(D12-D13),2)
gt;
gt;
gt; --
gt; rsenn
gt; ------------------------------------------------------------------------
gt; rsenn's Profile: www.excelforum.com/member.php...oamp;userid=29050
gt; View this thread: www.excelforum.com/showthread...hreadid=515132
gt;
gt;

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
groups.google.com/group/micro...06871cf92f8465
to be useful.

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

Jerry

quot;peabrain25quot; wrote:

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