#### 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
```
 0
Colin
6/5/2010 9:07:18 PM
excel.worksheet.functions 4936 articles. 2 followers.

9 Replies
1792 Views

Similar Articles

[PageSpeed] 53

```=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

```
 0
Don
6/5/2010 9:32:55 PM
```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
>

```
 0
David
6/5/2010 9:42:00 PM
```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?

Best Wishes
```
 0
Colin
6/5/2010 9:47:38 PM
```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.

>

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

```
 0
Colin
6/5/2010 10:02:14 PM
```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
>>
>

```
 0
Don
6/5/2010 10:18:19 PM
```>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.
>
>
>
>>
>
>
>>
>>"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
>>>
>>
>

```
 0
T
6/6/2010 2:02:55 AM
```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
Shikata ga nai...
```
 0
Stan
6/6/2010 11:51:45 AM
```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
```
 0
Colin
6/6/2010 2:35:57 PM
```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

```
 0
T
6/6/2010 4:05:36 PM

Similar Artilces:

if formula #5
i have data in sheet 1 and if the column AG has agent code 1,2,3,4, and so on i have creatred different sheets for the agents i want when the data is inputed in sheet 1 and when column AG is updated the information should be copied to respective sheet. -- Nisha P Hi -Navigate to the cell in the sheet that needs to update automatically -Press the equals (=) key -Navigate to and click on the cell that contains the value required -Press Enter -- Steve "nishkrish" <nishkrish@discussions.microsoft.com> wrote in message news:B0753B6E-DFFA-4DEF-BF89-D65A8C2424D0@microsof...

formula auditing/macro
Can anyone give me the sytax to goto - special - precedents so I can create a macro so I can assign to a hotkey and dont have to go through 4 steps ? Thanks, Yosef With A1=1 and D2=2*A1, and D1 as active cell: I recorded a macro for these steps: Edit|GoTo->Special->Precedence And the macro contained just one line: Selection.DirectPrecedents.Select best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "ynissel" <ynissel@discussions.microsoft.com> wrote in message news:DA544BDE-3717-4953-A5E3-06191BC28373@microsoft.com... > Can anyone...

formulas-
what is the proper way of entering a formula if you want to sum colum a if colum b is >03-01-05 but <04-01-05 One way: =SUMPRODUCT((B1:B100>DATEVALUE("03/01/05"))*(B1:B100<DATEVALUE("04/01/05"))* A1:A100) You do realize, that your parameters will *not* sum the entire month of March. What you posted (>03-01-05), will exclude the first day of the month. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------...

If Formula #4
Hi I have a formula in cell Y11 shown below =IF(ISERR(FIND("/",T1)),WORKDAY(Y10,6-WEEKDAY(Y10)),Y10-WEEKDAY(Y10,2)+8) I need add a new condition to this formula so that it only operate when the word DAILY appears in cell X10 X10 can have two values DAILY or 5 MIN if X10 shows 5 MIN then i want to use a new formula in cell Y11 =IF(X10="5 MIN",BB3) How can i combine these Many thank -- Message posted from http://www.ExcelForum.com Hi try =IF(X10="Daily",IF(ISERR(FIND("/",T1)),WORKDAY(Y10,6-WEEKDAY(Y10)),Y10- WEEKDAY(Y10,2)+8),"")...

Problem with formula when inserting a new column
Hi I have a spreadsheet similar to the below which is updated weekly A B C D NAME WEEKLY SCORE WEEKLY SCORE MEETING HELD John Smith 250 230 01/11/2009 Jane Smith 105 10 07/12/2009 I insert a new column at the beginning of every week to enter in the new score so we have a continual track. I want to create seperate spreadsheets for the team managers for their employees which will update...

can i short data with excel formula
if column A1 has any value and column A3 has any value and column A7 has any value , so can i short these value with excel formula..... You'll need to provide a clearer explanation of what you are looking for. A1, A3 and A7 are cells, not columns. What does "short these" mean? Do you mean "show these"? If so, is this what you are looking for: =if(and(a1<>"",a3<>"",a7<>""),true,false) Regards, Fred Best idea is to provide an example of what you want done. Regards, Fred "sunder jangra" <s...

Public Folder tree
We have some public folder trees that are getting 5-10 levels deep, as they are by default matching our projects drive folder structure. I am just wondering, is there a limit to how deep and wide a public folder matrix can/should go? I can easily see a structure of 20 projects, each as many as a hundred subfolders in a tree up to 10 levels deep, with literally thousands of files in each project. Am I courting disaster? Thanks, Gordon On Tue, 1 Nov 2005 11:02:09 -0800, Gordon Price <GordonPrice@discussions.microsoft.com> wrote: >We have some public folder trees that are getting...

Complex formula change making m,e crazy
I have thsi formula... myFormula = "=IF(ISERR(-TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))),""UNKNOWN""," _ & "IF(--TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))<999," _ & "VALUE(TRIM(RIGHT(SUBSTITUTE(H2,""/""," _ & "REPT("" "",100)),100))),""UNKNOWN"...

Formula Help #62
This is a tricky one but hopefully someone will be able to help. I need to create a formula that can will give me an updated date based on dates. I have a effective date and term of contract. Now if the contract is renewable I want the formula to be able to look at the effective date and extend it out by the term, say 5 years, and give me the new date if that day hasn't passed the current day. If it has then extend the contract by the term again. My thought is that only a very intricate IF function will work. I think I have it but am having trouble getting into formula terms....

Extract worksheet name from a chart formula
Hi, I want to write a small program that will give me the name of all the worksheets that are referenced by chart series. I have tried to look at the chart series formula but am struggling to write code that can account for all the variants, is there an easy way to do this, or does someone have some sample code? Thanks in advance! Hi, Try John Walkenbach's class object, http://spreadsheetpage.com/index.php/tip/a_class_module_to_manipulate_a_chart_series/ Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Simon Reid" <Sim...

Printing Complex Workbooks
Hi -- I am wondering if anyone has experienced this issue. Some of my users have created very complex workbooks, with different page orientations, formulas, colors and different page sizes. When these documents are printed (NDPS), Excel crashes. This only happens when the entire workbook is printed. Not the active page. The only solution is to use an older version of the Novell print driver - NDPPNT.DLL. Environment specs: Windows 2000 Excel 2000 Netware 5.1 SP6 Novell Client 4.90 HP PCL 5E (latest firmware on printers, latest version of PCL 5E driver) Novell's stance is if pr...

query is too complex error message
does anyone know if access 2007 has increased the capabilities of their queries? i have a query that was built in the 2003 version that keeps giving me the subject line error message. the query includes 7 fields that have a criteria requirements. it works for about a month and then just stops working. any help would be appreciated. thanks,andrea-- Andrea On Tue, 27 Mar 2007 18:13:51 -0700, Andrea <Andrea@discussions.microsoft.com>wrote:>the query criteria is as follows:>>([forms]![frmSearchByMultipleFunctions]![ThisValue5] >or([forms]![frmSearchByMultipleFunctions]![ThisVa...

Why won't pasted values from a formula appear in a pivot table
I am trying to make a pivot table from an array of information that was the result of formulas on another worksheet page. I used a macro to copy these formulas on the other worksheet page, then paste their values in the array that I need a pivot table from. The pivot table will not sum any of the values in the array and it does not combine cells in one column that are alike if their corresponding cells in the adjacent column are different. Everything works if I retype the data instead of copying and pasting. This is not an option becuase there are several thousand lines of data. Th...

Using complex formula in Diagram,AutoShape,WordArt,Organization Ch
Download and free try AddinTools Create from http://www.addintools.com Make you using formulas and cell reference in Excel objects: diagram, autoshape, wordart, and organization chart. To get thousands of Diagrams, AutoShapes, WordArts, Organization Charts, Charts, Reports, Tabs, Cards, and Forms that contain different values in several minutes! The quantity of results that you can produce depends on the volume of data that saved in worksheets. Fully Utilize The Data In Workbooks. The data in results may source from the same worksheet, and may also source from one or more workbooks and...

need help..Simple, compound, complex, compound-complex examples
Need to write a paragraph on the four sentence types...Just need to have an example of each type....please someone help!! Thanks On 30/05/2010 4:18 PM, Peggylynne wrote: > Need to write a paragraph on the four sentence types...Just need to have an > example of each type....please someone help!! Thanks See http://www.eslbee.com/sentences.htm and some of the other sites that appear when you google for the subject of your post. -- Hope this helps, Doug Robbins - Word MVP Please reply to the newsgroup unless you want to obtain my services on a professional basis. ...

Formula auditing on protected sheets
Simple question: is there a easy (or difficult?) way to track precedent and dependent cells on sheets which are protected? Thanks, Andrew ...

IF formula and whole numbers
With regards to an 'IF' formula, how do i get it to reconise whole numbers only. For example, if the result was either 1,2,3,4,5,6.... the answer would be true but if the result was either 1.5,2.7,3.2 etc and not a whole number, then the answer would be false. i can get it to recognise a single whole number but not multiple whole numbers. I know there is a very simple answer to this but i am new to this game. Many Thanks James -- jdmagoo ------------------------------------------------------------------------ jdmagoo's Profile: http://www.excelforum.com/member.php?action=get...

Formulas!?!??
I have an Excel spreadsheet that lists all employees. Column C is headed "status" (active/terminated) and Column E is headed "tenure" (numerical value of years w/company). I would like to calculate the average tenure for all active employees.....do you know if this is possible and if so how I can do it? Thanks!!!! =AVERAGE(IF(C2:C1000="active",E2:E1000) which is an array formula, so commit with Ctrl-Shift-Enter -- HTH RP (remove nothere from the email address if mailing direct) "jbressma" <jbressma@discussions.microsoft.com> wro...

Problem with formula
Have attached a worksheet. In the given worksheet, the cell AB2 should reduce if data is not entered between N2:Y2. Basically the total scoring is out of 100. Cells between N2 and Y2 have different weightages. Hence according to the cell which is blank, ab2 should reduce the cell value. Please help construct a formula for the same... -- Sundaram Iyer ------------------------------------------------------------------------ Sundaram Iyer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23906 View this thread: http://www.excelforum.com/showthread.php?threadid=37...

If formula #5
Please help with a formula using "IF". If one cell says "LP108" then another cell should be mulitiplied by .06 --- If not it should be multiplied by .04. Thanks for the help. If B1 is where the LP108 may be and C1 is the cell you want multiplied, enter this in B2 =IF(B1="LP108",C1*.06,C18*.04) "Mike" <ronky22deletethis@aol.com> wrote in message news:16fcc01c448cd\$76a1b060\$a601280a@phx.gbl... : Please help with a formula using "IF". If one cell : says "LP108" then another cell should be mulitiplied : by .06 --- If not...

Formula For Calculating DB Growth
I've heard there's a formula that would help you estimate the DB growth rate/size, based on the number customers, items, transactions, etc... Anybody can point me to where I can find it? Thanks, Probably best to just take the weekly growth over 6 weeks, and use the average for your estimation. "Tom Bombadill" wrote: > I've heard there's a formula that would help you estimate the DB growth > rate/size, based on the number customers, items, transactions, etc... > > Anybody can point me to where I can find it? > > Thanks, > > > ...

complex formula help!
I'm designing a spreadsheet to log specific details based on the present date and time. Basically if an entry is logged on a day from Mon-Fri before 5pm, then a cell value will have the present date. If it is logged anytime on sat/sun the cell will have the next working day (Monday) date. If an entry is logged after 5pm, Mon-Thurs, then the next working day date is entered into the cell. For friday this would mean Monday's date. I have the individual formulas for each of these situations. However i need to integrate the formulas into a user form so that all this is done at the click of...

Complicated Formula #2
Hi. I'm trying to do a very complicated formula. I have a list of ten numbers in a row and I have one cell which is telling me the max of those ten numbers. This new cell I want to be able to find the number that was returned from the above cell and then create a formula from that point. I want the formula to keep looking back (going down the excel spreadsheet) looking for the lowest number in a row from that number, and divide the current number by that number. Example: From say a1:a10 I have : 2, 6, 4, 8, 4, 9, 3, 2, 1, 4 Cell one returns - 9 Cell two would look what came before...

Creating a formula in Excel
How do I create a formula to update weekly PTO time? I have to keep up with 300 employees PTO time on a weekly basis. Example: I accumlate 1.54 hours at the end of every week with the week ending every Sunday. So starting 1/10/2010 I started off with 1.54 hours. Now, on 2/28/2010 I have accumlated 12.32 hours. I have people constantly asking me how many hours do they have. I would like to create a spreadsheet with all the employees names on it and have it update every Sunday, so that I will not have to go to the calculator for all these employees. Could someone give me an example...

How do I check a formula?
Is there a program or a website where I can insert my formula and have it checked. Is there an add-on of some sort? I use Excel2000. checked for what? -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "jnord" <jnord@discussions.microsoft.com> wrote in message news:1E228B78-C59A-44B7-86C5-2F10C8F6196F@microsoft.com... > Is there a program or a website where I can insert my formula and have it > checked. Is there an add-on of some sort? I use Excel2000. jnord, Post it here. You'll have dozens of reviewers.....