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


"Notbefore10" wrote in message
news:WqwBh.93895$Oa.50528@edtnps82...
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.

It's probably a rounding issue.

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

Biff

"George Gee" wrote in message
...
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


"Notbefore10" wrote in message
news:WqwBh.93895$Oa.50528@edtnps82...
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 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

"George Gee" wrote in message
...
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


"Notbefore10" wrote in message
news:WqwBh.93895$Oa.50528@edtnps82...
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.

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

"Notbefore10" wrote in message news:FlzBh.84823$Y6.57766@edtnps89...
| 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
|
| "George Gee" wrote in message
| ...
| 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
|
|
| "Notbefore10" wrote in message
| news:WqwBh.93895$Oa.50528@edtnps82...
| 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.


"Niek Otten" wrote in message
...
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

"Notbefore10" wrote in message
news:FlzBh.84823$Y6.57766@edtnps89...
| 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
|
| "George Gee" wrote in message
| ...
| 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
|
|
| "Notbefore10" wrote in message
| news:WqwBh.93895$Oa.50528@edtnps82...
| 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.
|
|
|
|
|

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)



"Notbefore10" wrote in message
news:P%ABh.86980$Fd.17832@edtnps90...
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.


"Niek Otten" wrote in message
...
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

"Notbefore10" wrote in message
news:FlzBh.84823$Y6.57766@edtnps89...
| 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
|
| "George Gee" wrote in message
| ...
| 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
|
|
| "Notbefore10" wrote in message
| news:WqwBh.93895$Oa.50528@edtnps82...
| 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.
|
|
|
|
|

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

"Notbefore10" wrote:

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.


"Niek Otten" wrote in message
...
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

"Notbefore10" wrote in message
news:FlzBh.84823$Y6.57766@edtnps89...
| 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
|
| "George Gee" wrote in message
| ...
| 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
|
|
| "Notbefore10" wrote in message
| news:WqwBh.93895$Oa.50528@edtnps82...
| 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.
|
|
|
|
|

Try =IF(ABS(D75-D76)<0.001,"Balanced","Error")
Like Jerry, I thnik you have an IEEE rounding issue here
best wishes

"Notbefore10" wrote in message
news:WqwBh.93895$Oa.50528@edtnps82...
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.

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


"Bernard Liengme" wrote in message
...
Try =IF(ABS(D75-D76)<0.001,"Balanced","Error")
Like Jerry, I thnik you have an IEEE rounding issue here
best wishes

"Notbefore10" wrote in message
news:WqwBh.93895$Oa.50528@edtnps82...
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.

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

"Notbefore10" wrote in message
news:w5IBh.89396$Y6.79739@edtnps89...
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


"Bernard Liengme" wrote in message
...
Try =IF(ABS(D75-D76)<0.001,"Balanced","Error")
Like Jerry, I thnik you have an IEEE rounding issue here
best wishes

"Notbefore10" wrote in message
news:WqwBh.93895$Oa.50528@edtnps82...
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.

Just to add...

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

Notbefore10 wrote:

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

"Bernard Liengme" wrote in message
...
Try =IF(ABS(D75-D76)<0.001,"Balanced","Error")
Like Jerry, I thnik you have an IEEE rounding issue here
best wishes

"Notbefore10" wrote in message
news:WqwBh.93895$Oa.50528@edtnps82...
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.



--

Dave Peterson

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

"Notbefore10" wrote in message
news:WqwBh.93895$Oa.50528@edtnps82...
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.

Just think how much you could embezzle if you had millions of these
transactions!

Biff

"Notbefore10" wrote in message
news:3zKBh.88789$Fd.84597@edtnps90...
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

"Notbefore10" wrote in message
news:WqwBh.93895$Oa.50528@edtnps82...
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.

Superman III

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"T. Valko" wrote:

Just think how much you could embezzle if you had millions of these
transactions!

Biff

"Notbefore10" wrote in message
news:3zKBh.88789$Fd.84597@edtnps90...
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

"Notbefore10" wrote in message
news:WqwBh.93895$Oa.50528@edtnps82...
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.