problem with IF statement

I am comparing two columns with a IF statement and returning "Yes" if they're equal and "No" if they're not. However, I'm getting a "No" in cases where the values are equal. The formula works correctly if I select each cell and press enter. However, my sheet is too long to do this for every cell.

I believe this is due to concatenating/comparing two text columns and I have seen this issue before but I can't remember the remedy. Thanks for any help.

wolfpack95,

The most likely reason for comparisons failing is that the values only look
the same but are very slightly different. For example if one cell has =1/9
and the other a constant of 0.111111 they will both look the same in the
cell but a test of =A1=A2 will return FALSE because 1/9 is 0.111 recurring.
The answer is to round the data before comparing it as in:

=ROUND(A1,6)=ROUND(A2,6)

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"wolfpack95" <[email protected]> wrote
in message news:[email protected]...
>
> I am comparing two columns with a IF statement and returning "Yes" if
> they're equal and "No" if they're not. However, I'm getting a "No" in
> cases where the values are equal. The formula works correctly if I
> select each cell and press enter. However, my sheet is too long to do
> this for every cell.
>
> I believe this is due to concatenating/comparing two text columns and I
> have seen this issue before but I can't remember the remedy. Thanks for
> any help.
>
>
> --
> wolfpack95
> ------------------------------------------------------------------------
> wolfpack95's Profile:
> http://www.excelforum.com/member.php...info&userid=93
> View this thread: http://www.excelforum.com/showthread...hreadid=573881
>

Thanks Sandy but that doesn't appear to be my problem.

The formula will display the correct results if I double click and hit Enter on one of the cells being compared. I vaguely remember a Clean function posted on here a while back that might do the trick however I haven't been able to locate it.

Please post the relevant formulas

You may want to check your formatting. If you are comparing numbers and one cell is formatted as a number and the other is formatted as text, you may get the results you described.