What is wrong with this formula?

  • Thread starter Thread starter Notbefore10
  • Start date Start date
N

Notbefore10

I get the opposite effect when I apply this formula...

=IF(D75=D76,"Balanced","Error")

The answer is "Error", even though the amount is exactly the same in D75
and D76.

If I reverse it I still get the wrong answer...

=IF(D75<>D76,"Error","Balanced"

The answer is "Balanced"

I must be missing some very basic syntax knowledge. The cells D75 and
E76 are definitely the same number format.

Thanks for your help.
 
Not sure, it works for me.
But as your last paragraph mentions D75 and E76,
should the formula not be:

=IF(D75=E76,"Balanced","Error")

George Gee
 
Sorry, that was a typo. I meant =IF( D75=D76),"Balanced,"Error")

This really has be stumped. The format of D75 and D76 is a simple 2
decimal number. Could it be something to do with D75 and D76 each having
a formula of its own?

Thanks
 
Format both cells as General and make the column wide enough to display all decimals.
You'll probably see they are not equal.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Sorry, that was a typo. I meant =IF( D75=D76),"Balanced,"Error")
|
| This really has be stumped. The format of D75 and D76 is a simple 2
| decimal number. Could it be something to do with D75 and D76 each having
| a formula of its own?
|
| Thanks
|
| | > Not sure, it works for me.
| > But as your last paragraph mentions D75 and E76,
| > should the formula not be:
| >
| > =IF(D75=E76,"Balanced","Error")
| >
| > George Gee
| >
| >
| > | >>I get the opposite effect when I apply this formula...
| >>
| >> =IF(D75=D76,"Balanced","Error")
| >>
| >> The answer is "Error", even though the amount is exactly the same in
| >> D75 and D76.
| >>
| >> If I reverse it I still get the wrong answer...
| >>
| >> =IF(D75<>D76,"Error","Balanced"
| >>
| >> The answer is "Balanced"
| >>
| >> I must be missing some very basic syntax knowledge. The cells D75 and
| >> E76 are definitely the same number format.
| >>
| >> Thanks for your help.
| >>
| >
| >
|
|
 
Sorry, that didn't work either. I am beginning to be a bore I'm afraid.

The figures are all 2 decimal dollar amounts. No fractional
calculations are involved at all. It is just a simple accounting
workbook with one sheet for transaction detail and another sheet for
summary amounts by G/L code..

D75 on the Summary worksheet has this formula
=SUM(Debits)-SUM(Credits)-45550.14 - where Debits and Credits are on
the Detail worksheet

D76 on the Summary worksheet has this formula
=SUM(D16+D45+(D52*-1)+(D53*-1)+(D54*-1)+(D55*-1)+(D56*-1)+(D60*-1)) -
where these figures come from the Summary worksheet

The result of the formulas are 4434.32 in D75 and 4434.32 in D76, which
is correct.

Then I enter this formula in cell D77 on the Summary worksheet

=IF(D75=D76,"Balanced","Error")

and it comes up "Error"

Gr-r-r-r.
 
It must be rounding. Change the format of the cells to 0.000000000 and see
if the numbers are still the same, and increase the number of decimal
places, you will see them differ at some point.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
What do you get for
=(D76-D77)
Note that the parentheses are needed for this purpose.

The issue is that computers do binary math, but in binary most terminating
decimal fractions are non-terminating binary fractions that can only be
approximated (just as 1/3 can only be approximated in decimal). When you do
math with approximate inputs, it should be no surprise when the output is
also only approximate.

In fact the only 2-place decimal fractions that can be exactly represented
are .00, .25, .50, and .75.

Since you are only adding and subtracting numbers that each have no more
than 2 decimal places, you can round the result to 2 decimal places without
violence to the calculations and thus mask the impact of the binary
approximations to the inputs.

Excel will display nor more than 15 significant figures, which often makes
it difficult to see where these differences are coming from. However, to get
different results from
=IF(D75=D76,"Balanced","Error")
and
=IF(D75<>D76,"Error","Balanced")
at least one of D75:D76 should show a difference from 4434.32 when formatted
to show 11 decimal places. Niek was on the right track, but the General
format will show no more than 6 decimal places (regardless of column width)
for numbers in this range.

Jerry
 
Try =IF(ABS(D75-D76)<0.001,"Balanced","Error")
Like Jerry, I thnik you have an IEEE rounding issue here
best wishes
 
Yes!!!! Jerry, using the ABS worked! It never occurred to me after
years of working with Excel that when all cells were formatted as 2
decimal numbers there could still be a rounding error. Live and learn.

Thank you all ever so much.

Joan
 
You'd be better off "fixing" the problem at its source rather than
downstream.

Use Round in your formulas in cells D75 and D76.

Biff
 
Just to add...

Applying a format to show just two decimal places doesn't change the value in
the cell.
 
Hi All helpers,

Just to finish this off... both the Round formula provided by Biff and
the ABS formula provided by Jerry work perfectly.

But no wonder it was hard to figure out. When I added a credit of .01
cent to the detail, the original formula worked. When I added a debit
of one cent to the detail, the original formula did not work, but adding
a debit of .02 cents made it work.

If only the balancing figure had been anything other than 4434.32 I
might never have discovered the secret of Excel binary calculations
which you all have so generously provided.

Thanks again,
Joan
 
Back
Top