IF function compare to identical amount but inconsistent results

[skhuon]

I tried to compare the amount of 2 colums (10 rows) because and ithey 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 and when I eye-balled the two amounts, they are identical. So I am not sure why two identical amounts would give me "false" results. I tried to 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.

[Excelenator]

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. One caveat though, selecting this will change your data so make a backup copy of the data first.

I tried to compare the amount of 2 colums (10 rows) because and ithey 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 and when I eye-balled the two amounts, they are identical. So I am not sure why two identical amounts would give me "false" results. I tried to 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.

[Ron Rosenfeld]

On Fri, 28 Jul 2006 17:06:57 -0400, skhuon
<[email protected]> wrote:

>
>I tried to compare the amount of 2 colums (10 rows) because and ithey
>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 and
>when I eye-balled the two amounts, they are identical. So I am not sure
>why two identical amounts would give me "false" results. I tried to
>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.

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

[Ragdyer]

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.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"skhuon" <[email protected]> wrote in
message news:[email protected]...
>
> I tried to compare the amount of 2 colums (10 rows) because and ithey
> 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 and
> when I eye-balled the two amounts, they are identical. So I am not sure
> why two identical amounts would give me "false" results. I tried to
> 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.
>
>
> --
> skhuon
> ------------------------------------------------------------------------
> skhuon's Profile:
> http://www.excelforum.com/member.php...o&userid=36902
> View this thread: http://www.excelforum.com/showthread...hreadid=566181
>