IF function compare to identical amount but inconsistent results

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

skhuon - 28 Jul 2006 22:06 GMT

I tried to compare the amount of 2 colums (10 rows) because and ithe
are the same amount extracted from 2 different sources.
Here is my function =IF(B7=E7,TRUE,False)

Some rows came out to be True, however, I get some "False" results an
when I eye-balled the two amounts, they are identical. So I am not sur
why two identical amounts would give me "false" results. I tried t
format both columns to 2 decimal places and it still doesn't work.
Can someone please tell me how to fix this problem?
Thanks so much

Reply to this Message

Excelenator - 28 Jul 2006 22:34 GMT

You may use the EXACT function instead

=EXACT(B7,F7) which will return TRUE or FALSE. As for the inconsistent
results meerly formatting the two columns to two decimals will not make
the VALUES equal. For example 5.21111 formatted to two decimals is NOT
EQUIVALENT to 5.21. Excel STILL looks to the full precision of the value
UNLESS you select "Precision as displayed" under Tools Options
Calculations.

skhuon Wrote:

> I tried to compare the amount of 2 colums (10 rows) because and ithey
> are the same amount extracted from 2 different sources.
[quoted text clipped - 6 lines]
> Can someone please tell me how to fix this problem?
> Thanks so much.

Signature

Excelenator

Reply to this Message

Ron Rosenfeld - 28 Jul 2006 22:45 GMT

>I tried to compare the amount of 2 colums (10 rows) because and ithey
>are the same amount extracted from 2 different sources.
[quoted text clipped - 6 lines]
>Can someone please tell me how to fix this problem?
>Thanks so much.

The two results are NOT identical. That's why you have a false result.

Formatting does not change the stored number, only the displayed number (unless
you have selected Precision as Displayed in Tools/Options).

If two decimals of precision is what you require, you could rewrite your
formula:

=IF(ROUND(B7,2)=ROUND(E7,2),TRUE,FALSE)

or, if all you want is a true or false output:

=ROUND(B7,2)=ROUND(E7,2)

or even

=ABS(E7-B7)<=0.005

--ron

Reply to this Message

Ragdyer - 28 Jul 2006 22:53 GMT

See answer in the "Functions" group.

Please *DON'T* multipost.

All the regulars read all the groups, and it's a waste of their time when
suggestions are duplicated for no reason.

Signature

Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

>
> I tried to compare the amount of 2 colums (10 rows) because and ithey
[quoted text clipped - 7 lines]
> Can someone please tell me how to fix this problem?
> Thanks so much.

Reply to this Message

Rate this thread: