If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Complex AND OR Formula
HI I need to express something in a formula , and am having trouble with it. I need to say this : IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 , OTHERWISE PUT J2 Can someone assist with some code to make this happen , please? Grateful for any help. Sorry for double post. Best Wishes |
#2
|
|||
|
|||
Complex AND OR Formula
=IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2)
-- Don Guillett Microsoft MVP Excel SalesAid Software "Colin Hayes" wrote in message ... HI I need to express something in a formula , and am having trouble with it. I need to say this : IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 , OTHERWISE PUT J2 Can someone assist with some code to make this happen , please? Grateful for any help. Sorry for double post. Best Wishes |
#3
|
|||
|
|||
Complex AND OR Formula
I assume that you intended =IF(AND(RIGHT(E2,2)="bx",j2=11),1,j2) ?
"bx", not "1bx", as you've got 2, not 3, as the second parameter of the right function? -- David Biddulph "Don Guillett" wrote in message ... =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2) -- Don Guillett Microsoft MVP Excel SalesAid Software "Colin Hayes" wrote in message ... HI I need to express something in a formula , and am having trouble with it. I need to say this : IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 , OTHERWISE PUT J2 Can someone assist with some code to make this happen , please? Grateful for any help. Sorry for double post. Best Wishes |
#4
|
|||
|
|||
Complex AND OR Formula
In article , Don Guillett
writes =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2) Hi Don OK Thanks for that. I can't quite get it to work though , for what I have in mind. I found that this works for a single search parameter : =IF(AND(COUNTIF(E2,"*1bx*"),COUNTIF(J2,"11")),1,J2 ) but of course it only looks for "*1bx*". I need in incorporate an OR expression to also search for "*2bx*" , "*3bx*" and "*4bx*" in the same formula. Any ideas how to work these other values in? Thanks for your help. Best Wishes |
#5
|
|||
|
|||
Complex AND OR Formula
In article , David Biddulph
groups@[at] writes I assume that you intended =IF(AND(RIGHT(E2,2)="bx",j2=11),1,j2) ? "bx", not "1bx", as you've got 2, not 3, as the second parameter of the right function? -- David Biddulph Hi David Well no , not quite. The formula would need to look specifically for any of the 4 phrases ( "*1bx*" OR "*2bx*" OR "*3bx*" OR "*4bx*") contained anywhere in E2. If it finds any of these , and J2=11 , then put 1. If it doesn't find any of these , put J2. I would be dragging this down , so it would look in lower Cells in E also. There are other phrases containing the 'bx' suffix in E2 which I would want it to ignore. So '5bx' , '6bx' would be ignored for example. For this reason , it's not enough just to find 'bx'. I wouldn't want the issued clouded by the fact that the expression contains similar letters. The formula would need to identify them discretely , in the same way as if it were looking for pink , blue , green or yellow. Thanks for your help. "Don Guillett" wrote in message ... =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2) -- Don Guillett Microsoft MVP Excel SalesAid Software "Colin Hayes" wrote in message ... HI I need to express something in a formula , and am having trouble with it. I need to say this : IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 , OTHERWISE PUT J2 Can someone assist with some code to make this happen , please? Grateful for any help. Sorry for double post. Best Wishes |
#7
|
|||
|
|||
Complex AND OR Formula
The formula would need to look specifically
for any of the 4 phrases ("*1bx*" OR "*2bx*" OR "*3bx*" OR "*4bx*") contained anywhere in E2. Try this... =IF(COUNT(SEARCH({1,2,3,4}&"BX",E2))*(J2=11),1,J2) -- Biff Microsoft Excel MVP "Colin Hayes" wrote in message ... In article , David Biddulph groups@[at] writes I assume that you intended =IF(AND(RIGHT(E2,2)="bx",j2=11),1,j2) ? "bx", not "1bx", as you've got 2, not 3, as the second parameter of the right function? -- David Biddulph Hi David Well no , not quite. The formula would need to look specifically for any of the 4 phrases ( "*1bx*" OR "*2bx*" OR "*3bx*" OR "*4bx*") contained anywhere in E2. If it finds any of these , and J2=11 , then put 1. If it doesn't find any of these , put J2. I would be dragging this down , so it would look in lower Cells in E also. There are other phrases containing the 'bx' suffix in E2 which I would want it to ignore. So '5bx' , '6bx' would be ignored for example. For this reason , it's not enough just to find 'bx'. I wouldn't want the issued clouded by the fact that the expression contains similar letters. The formula would need to identify them discretely , in the same way as if it were looking for pink , blue , green or yellow. Thanks for your help. "Don Guillett" wrote in message ... =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2) -- Don Guillett Microsoft MVP Excel SalesAid Software "Colin Hayes" wrote in message ... HI I need to express something in a formula , and am having trouble with it. I need to say this : IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 , OTHERWISE PUT J2 Can someone assist with some code to make this happen , please? Grateful for any help. Sorry for double post. Best Wishes |
#8
|
|||
|
|||
Complex AND OR Formula
On Sat, 5 Jun 2010 22:07:18 +0100, Colin Hayes wrote:
I need to say this : IF E2 CONTAINS "1bx" OR "2bx" OR "3bx" OR "4BX" AND J2 =11 THEN PUT 1 , Then why don't you read the numerous responses that were posted in microsoft.public.excel.misc (including one from me)? Please do not post the same question multiple times. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com Shikata ga nai... |
#9
|
|||
|
|||
Complex AND OR Formula
In article , T. Valko
writes The formula would need to look specifically for any of the 4 phrases ("*1bx*" OR "*2bx*" OR "*3bx*" OR "*4bx*") contained anywhere in E2. Try this... =IF(COUNT(SEARCH({1,2,3,4}&"BX",E2))*(J2=11),1,J2 ) HI Biff Yes , that's got it. Perfect first time. Thanks for your time and expertise. Best Wishes |
#10
|
|||
|
|||
Complex AND OR Formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Colin Hayes" wrote in message news In article , T. Valko writes The formula would need to look specifically for any of the 4 phrases ("*1bx*" OR "*2bx*" OR "*3bx*" OR "*4bx*") contained anywhere in E2. Try this... =IF(COUNT(SEARCH({1,2,3,4}&"BX",E2))*(J2=11),1,J 2) HI Biff Yes , that's got it. Perfect first time. Thanks for your time and expertise. Best Wishes |
Thread Tools | |
Display Modes | |
|
|