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

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?

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

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Marie FP" wrote:
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?

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

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

Tryo

"Marie FP" wrote in message
...
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?

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

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

"Max" wrote:

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Marie FP" wrote:
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?

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

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?


"Tyro" wrote:

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

Tryo

"Marie FP" wrote in message
...
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?

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

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",""))
--
David Biddulph

"Marie FP" wrote in message
...
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?


"Tyro" wrote:

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

Tryo

"Marie FP" wrote in message
...
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?

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

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!


"David Biddulph" wrote:

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",""))
--
David Biddulph

"Marie FP" wrote in message
...
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?


"Tyro" wrote:

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

Tryo

"Marie FP" wrote in message
...
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?

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

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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Marie FP" wrote in message
...
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