Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to compare numbers in 3 columns | Excel Discussion (Misc queries) | |||
compare and find similar numbers in two columns | New Users to Excel | |||
How do I compare tow columns of numbers? | Excel Discussion (Misc queries) | |||
Compare text/numbers in different columns. | Excel Discussion (Misc queries) | |||
How do I compare two Excel columns of numbers and see non-matches | Excel Worksheet Functions |