Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And Or Formula
Hi,
I have a couple of previous posts on formulas, I'm not great at tagging them together so if anyone can point me in the direction of a good way to learn this I would appreciate it. In my previous posts I was offered 2 formulas, actually I now need to use them together to say that if the first formula is true call it say "PT1" and if the second condition is true call it "PT2" My 2 individual formulas are =IF(AND(LEFT(F2,3)=RIGHT(F2,3),LEN(F2)=11),"yes"," no") - this is the one I would want to return PT1 The second one is.... ==IF(AND(LEFT(H53076,3)=RIGHT(H53076,3),LEN(H53076 )=19),"YES","NO") - this one would want to return PT2, and I also wanted to tag a condition to the second one to say the length should be 19 char. Is it possible to tag these 2 together and have the different answers, pt1,pt2, or NA for any others? Many thanks Paula |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And Or Formula
I would think this:
=IF(AND(LEFT(F2,3)=RIGHT(F2,3),LEN(F2)=11),"PT1",I F(AND(LEFT(H53076,3)=RIGHT(H53076,3),LEN(H53076)=1 9),"PT2","NA")) However, If the result will ALWAYS be one or the other, then it becomes simpler. =IF(AND(LEFT(F2,3)=RIGHT(F2,3),LEN(F2)=11),"PT1"," PT2") HTH, Paul -- "Paula" wrote in message ... Hi, I have a couple of previous posts on formulas, I'm not great at tagging them together so if anyone can point me in the direction of a good way to learn this I would appreciate it. In my previous posts I was offered 2 formulas, actually I now need to use them together to say that if the first formula is true call it say "PT1" and if the second condition is true call it "PT2" My 2 individual formulas are =IF(AND(LEFT(F2,3)=RIGHT(F2,3),LEN(F2)=11),"yes"," no") - this is the one I would want to return PT1 The second one is.... ==IF(AND(LEFT(H53076,3)=RIGHT(H53076,3),LEN(H53076 )=19),"YES","NO") - this one would want to return PT2, and I also wanted to tag a condition to the second one to say the length should be 19 char. Is it possible to tag these 2 together and have the different answers, pt1,pt2, or NA for any others? Many thanks Paula |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And Or Formula
Hi Paul,
Thanks so much for your answer, I have missed a bit out... the second part of the formulas should be =IF((LEFT(A1,3)&MID(A1,5,3))=(RIGHT(A1,3)&MID(A1,1 3,3)),"YES","NO") and again I'd like to merge 19 characters long into the second part... Apologies for the confusion. Many thanks Paula "PCLIVE" wrote: I would think this: =IF(AND(LEFT(F2,3)=RIGHT(F2,3),LEN(F2)=11),"PT1",I F(AND(LEFT(H53076,3)=RIGHT(H53076,3),LEN(H53076)=1 9),"PT2","NA")) However, If the result will ALWAYS be one or the other, then it becomes simpler. =IF(AND(LEFT(F2,3)=RIGHT(F2,3),LEN(F2)=11),"PT1"," PT2") HTH, Paul -- "Paula" wrote in message ... Hi, I have a couple of previous posts on formulas, I'm not great at tagging them together so if anyone can point me in the direction of a good way to learn this I would appreciate it. In my previous posts I was offered 2 formulas, actually I now need to use them together to say that if the first formula is true call it say "PT1" and if the second condition is true call it "PT2" My 2 individual formulas are =IF(AND(LEFT(F2,3)=RIGHT(F2,3),LEN(F2)=11),"yes"," no") - this is the one I would want to return PT1 The second one is.... this one would want to return PT2, and I also wanted to tag a condition to the second one to say the length should be 19 char. Is it possible to tag these 2 together and have the different answers, pt1,pt2, or NA for any others? Many thanks Paula |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And Or Formula
I'm not sure I follow.
What is it that you want to know if it is 19 characters long? Is it all of A1? If so, then you could start with: =IF(LEN(A1)=19,IF(LEFT(A1,3)&MID(A1,5,3)=RIGHT(A1, 3)&MID(A1,13,3),"YES","NO"),"Not 19 Characters") Does that help? Paul -- "Paula" wrote in message ... Hi Paul, Thanks so much for your answer, I have missed a bit out... the second part of the formulas should be =IF((LEFT(A1,3)&MID(A1,5,3))=(RIGHT(A1,3)&MID(A1,1 3,3)),"YES","NO") and again I'd like to merge 19 characters long into the second part... Apologies for the confusion. Many thanks Paula "PCLIVE" wrote: I would think this: =IF(AND(LEFT(F2,3)=RIGHT(F2,3),LEN(F2)=11),"PT1",I F(AND(LEFT(H53076,3)=RIGHT(H53076,3),LEN(H53076)=1 9),"PT2","NA")) However, If the result will ALWAYS be one or the other, then it becomes simpler. =IF(AND(LEFT(F2,3)=RIGHT(F2,3),LEN(F2)=11),"PT1"," PT2") HTH, Paul -- "Paula" wrote in message ... Hi, I have a couple of previous posts on formulas, I'm not great at tagging them together so if anyone can point me in the direction of a good way to learn this I would appreciate it. In my previous posts I was offered 2 formulas, actually I now need to use them together to say that if the first formula is true call it say "PT1" and if the second condition is true call it "PT2" My 2 individual formulas are =IF(AND(LEFT(F2,3)=RIGHT(F2,3),LEN(F2)=11),"yes"," no") - this is the one I would want to return PT1 The second one is.... this one would want to return PT2, and I also wanted to tag a condition to the second one to say the length should be 19 char. Is it possible to tag these 2 together and have the different answers, pt1,pt2, or NA for any others? Many thanks Paula |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
If And Or Formula
Hi Paul,
Sorry for the confusion... so if I have 2 examples of text lon/ams/lon in cell a2 as an example that would be an 11 character long with the first 3 and the last 3 characters matching. another example would be lon/par/nyc/par/lon in cell a2that would be 19 characters long with the first 3 and last 3 matching and also the par/par matches so positions 5-7 and 13 to 15 match, I am looking for a formula that if the first is true it says one thing, if the second is true it has a different response, but if neither of the above are true is says no. I have this at the moment in single formulas but I wanted to know if I could combine into one formula.. Thanks for your help... Paula "PCLIVE" wrote: I'm not sure I follow. What is it that you want to know if it is 19 characters long? Is it all of A1? If so, then you could start with: =IF(LEN(A1)=19,IF(LEFT(A1,3)&MID(A1,5,3)=RIGHT(A1, 3)&MID(A1,13,3),"YES","NO"),"Not 19 Characters") Does that help? Paul -- "Paula" wrote in message ... Hi Paul, Thanks so much for your answer, I have missed a bit out... the second part of the formulas should be =IF((LEFT(A1,3)&MID(A1,5,3))=(RIGHT(A1,3)&MID(A1,1 3,3)),"YES","NO") and again I'd like to merge 19 characters long into the second part... Apologies for the confusion. Many thanks Paula "PCLIVE" wrote: I would think this: =IF(AND(LEFT(F2,3)=RIGHT(F2,3),LEN(F2)=11),"PT1",I F(AND(LEFT(H53076,3)=RIGHT(H53076,3),LEN(H53076)=1 9),"PT2","NA")) However, If the result will ALWAYS be one or the other, then it becomes simpler. =IF(AND(LEFT(F2,3)=RIGHT(F2,3),LEN(F2)=11),"PT1"," PT2") HTH, Paul -- "Paula" wrote in message ... Hi, I have a couple of previous posts on formulas, I'm not great at tagging them together so if anyone can point me in the direction of a good way to learn this I would appreciate it. In my previous posts I was offered 2 formulas, actually I now need to use them together to say that if the first formula is true call it say "PT1" and if the second condition is true call it "PT2" My 2 individual formulas are =IF(AND(LEFT(F2,3)=RIGHT(F2,3),LEN(F2)=11),"yes"," no") - this is the one I would want to return PT1 The second one is.... this one would want to return PT2, and I also wanted to tag a condition to the second one to say the length should be 19 char. Is it possible to tag these 2 together and have the different answers, pt1,pt2, or NA for any others? Many thanks Paula |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|