Formula to compare numbers in 3 columns 3/23/08

[Marie FP]

I have three columns each with different numbers. Column A has only two
numbers, Column B has 7, and Column C has 5. I need to know when the numbers
in Column A and the first two numbers in both Columns B and C do not equal.
I created the formula =IF(OR(A1<>LEFT(B1,2),LEFT(C1,2)),"Doesn't Equal",""),
but am getting the “#VALUE!†error message. Any ideas on how to fix this?

 

[Max]

Try this revision:
=IF(OR(A1<>LEFT(B1,2)+0,A1<>LEFT(C1,2)+0),"Doesn't Equal","")

The "+0" is one way to coerce the text number returned by LEFT to a real
number so that it can be compared to A1's real number.

 

[Tyro]

Please give an example. Your question is simple to answer but could be
interpreted in different ways.

Tryo

 

[Marie FP]

Thanks, Max. I tried the formula, but it now shows a value of "Doesn't
Equal" in all cases, even when the first two numbers in B and C equals the
number in A. Also, when there are no numbers in either of the three columns,
the formula leaves the "#VALUE!†error message again. Is there something
else I can try?
Marie

 

[Marie FP]

Tyro,

A1 = 06
A2 = 0608NPR1
A3 = 06W500

The first two numbers in each column must equal. I would like a formula to
tell me when they don't. A1 will always be a 2-digit number, A2 and A3 will
have 7 and 5 digits, respectively. Does this help?

 

[Marie FP]

Sorry David, I didn't realize I posted to two separate threads and didn't see
the email notification telling me you responded. I just tried the formula
you first suggested, =IF(OR(--A1<>--LEFT(B1,2),--A1<>--LEFT(C1,2)),"Doesn't
Equal",""), and it works fine. Thanks to all for your help!

 

[Max]

As stated in my response, I had presumed you had real numbers in A1. The
earlier was meant to indicate the correct way to use OR, and to highlight
that LEFT will always return spliced numbers as text, unless coerced with an
arithmetic op, eg: the "+0". Anyway, its good to see you got it working
since, with David's help/response.