If formula - gives error on negative number

[dtm5]

I have a formula as follows IF(L23=Sheet1!G73,"BALANCES","ERROR")

However on a negative calculation it shows error even though the 2 figures match.

I've audited the formula and these are the results?

1. It evalutes L23, which contains the following formula SUM(L13:L22) and shows (8,370)

2. It shows in audit windows as IF(-8370.43000000001=Sheet1!G73,"BALANCES","ERROR")

3. Next step results in this
IF(-8370.43000000001=-8370.43,"BALANCES","ERROR")

4. Next step it evalutes IF(FALSE,"BALANCES","ERROR")

Can anyone explain why it does not balance? and how i can improve my formula?

[stevekirk]

hi,

you should not have a problem

= IF(L23=Sheet1!G73,"BALANCES","ERROR")


i have only put the = sign inf ront of it and it works fine
steve

[mangesh_yadav]

=IF(Round(L23,6)=Round(Sheet1!G73,6),"BALANCES","ERROR")

should do the trick. Sometimes the value resulting from a formula could be 1.999999999 or 1.000000001 when you are expecting 1. So a good bet is to use the round fuinction.

Mangesh

[dtm5]

Thanks Mangesh it works with that. Can you just help with this formula as well which is also giving an the same error on a negative figure

=IF((SUM(I73:W73)+E73)-X73=0,"BALANCES","ERROR")

[mangesh_yadav]

=IF(ROUND((SUM(I73:W73)+E73)-X73,6)=0,"BALANCES","ERROR")

Mangesh

[dtm5]

works a treat, thks for your help