Complex AND OR Formula - microsoft.public.excel.worksheet.functions

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

=IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message news:WY5OXLAGyrCMFwoB@chayes.demon.co.uk... > > 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

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" <dguillett1@gmail.com> wrote in message news:eucSraPBLHA.5476@TK2MSFTNGP06.phx.gbl... > =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2) > > -- > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguillett@gmail.com > "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message > news:WY5OXLAGyrCMFwoB@chayes.demon.co.uk... >> >> 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 >

In article <eucSraPBLHA.5476@TK2MSFTNGP06.phx.gbl>, Don Guillett <dguillett1@gmail.com> 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

In article <c5OdnVDaM7E0WZfRnZ2dnUVZ8uOdnZ2d@bt.com>, 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" <dguillett1@gmail.com> wrote in message >news:eucSraPBLHA.5476@TK2MSFTNGP06.phx.gbl... >> =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2) >> >> -- >> Don Guillett >> Microsoft MVP Excel >> SalesAid Software >> dguillett@gmail.com >> "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message >> news:WY5OXLAGyrCMFwoB@chayes.demon.co.uk... >>> >>> 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 >> >

I did!!! -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "David Biddulph" <groups [at] biddulph.org.uk> wrote in message news:c5OdnVDaM7E0WZfRnZ2dnUVZ8uOdnZ2d@bt.com... >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" <dguillett1@gmail.com> wrote in message > news:eucSraPBLHA.5476@TK2MSFTNGP06.phx.gbl... >> =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2) >> >> -- >> Don Guillett >> Microsoft MVP Excel >> SalesAid Software >> dguillett@gmail.com >> "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message >> news:WY5OXLAGyrCMFwoB@chayes.demon.co.uk... >>> >>> 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 >> >

>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" <Colin@chayes.demon.co.uk> wrote in message news:Gfx6WVAmlsCMFw6b@chayes.demon.co.uk... > In article <c5OdnVDaM7E0WZfRnZ2dnUVZ8uOdnZ2d@bt.com>, 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" <dguillett1@gmail.com> wrote in message >>news:eucSraPBLHA.5476@TK2MSFTNGP06.phx.gbl... >>> =IF(AND(RIGHT(E2,2)="1bx",j2=11),1,j2) >>> >>> -- >>> Don Guillett >>> Microsoft MVP Excel >>> SalesAid Software >>> dguillett@gmail.com >>> "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message >>> news:WY5OXLAGyrCMFwoB@chayes.demon.co.uk... >>>> >>>> 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 >>> >> >

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

In article <OKauixRBLHA.5464@TK2MSFTNGP05.phx.gbl>, T. Valko <biffinpitt@comcast.net> 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

You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Colin Hayes" <Colin@chayes.demon.co.uk> wrote in message news:p4LB$bANJ7CMFwMj@chayes.demon.co.uk... > In article <OKauixRBLHA.5464@TK2MSFTNGP05.phx.gbl>, T. Valko > <biffinpitt@comcast.net> 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

Similiar Articles:

complex formula - microsoft.public.excel.worksheet.functions ...
I need help creating a formula. I have 4 columns and 12 rows of data. When the number in the cell reaches multiples of 3 it creates 1 occurance. ...

Formula to calculate compound interest on increasing principal ...
Hi folks, Would appreciate some help in locating a formula to calculate compound interest on an increasing principal. Many thanks ...

Complex value lookup? (Excel 2003) - microsoft.public.excel.misc ...
So I modified the formula as > follows ... Complex value lookup? (Excel 2003) - microsoft.public.excel.misc ... So I modified the formula as > follows: > > =VLOOKUP(D1 ...

Return Value from cells which match criteria (complex) - microsoft ...
The formula needs to examine 4 columns for the start point, then 43 or so columns ... Return Value from cells which match criteria (complex) - microsoft ... I have a ...

complex lookup - microsoft.public.excel.worksheet.functions ...
Visual Basic :: Array Formula Complex Lookup Problem Array Formula Complex Lookup Problem Hi, Having a bit of trouble with a complex array formula in Excel - not sure this ...

Compound Interest Rate or Growth Rate - microsoft.public.excel ...
Formula to calculate compound interest on increasing principal ... Moreden, If you add to the principal at a constant rate, and the interest rate doesn't change, then ...

microsoft.public.excel.worksheet.functions
Complex AND OR Formula Colin 9 343 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 ...

Nested IF(OR(AND) Statement with Two AND Conditions - microsoft ...
I am using Excel 2003 and could use some assistance with a complex Nested IF ... Group 2 NAW1 N4 NWSA 90000000 BA The formula ...

extracting specific info from text cell. - microsoft.public.excel ...
So my thinking is either a complex formula that is eluding me, or possibly some VBA code. I am a novice VBA user so kind of stuck. Since this data is automated and ...

How do I access a previous version of my Excel document ...
I have made changes to my document, but need to be able to retrieve complex formula's back from a previous version. Is there any way i can get this ...

Complex Numbers & The Quadratic Formula - Purplemath
Demonstrates the relationship between complex roots of a quadratic and the intercepts of the related parabola. Also shows how to graph complex numbers.

Euler's formula - Wikipedia, the free encyclopedia
Euler's formula, named after Leonhard Euler, is a mathematical formula in complex analysis that establishes the deep relationship between the trigonometric functions ...

8/1/2012 6:32:11 AM