IF function compare to identical amount but inconsistent results

S

skhuon

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
 
E

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.

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.
Click to expand...
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
R

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.
 
Top