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?

 

[David Biddulph]

First problem, in your original question you were talking about 3 columns,
but in your example here you're talking about 3 rows.

Second question, in A1 you have 06. Is this a text entry, or is it a number
formatted as 00?

Third problem is that you started two separate threads, so the answers are
split. In the other thread I suggested
=IF(OR(A1<>--LEFT(B1,2),A1<>--LEFT(C1,2)),"Doesn't Equal",""), but if A1 may
not be a 2 digit number but may be text, then change it to
=IF(OR(--A1<>--LEFT(B1,2),--A1<>--LEFT(C1,2)),"Doesn't Equal","")
If you now want to work in rows instead of columns, change the B1 and C1 and
A2 and A3 respectively.

Fourth problem, in another branch of the thread you added yet another new
requirement and started talking about cases where you *don't* have entries
in the cells, in which case try changing my formula to
=IF(COUNTA(A1:C1)<3,"",IF(OR(--A1<>--LEFT(B1,2),--A1<>--LEFT(C1,2)),"Doesn't
Equal",""))

 

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