ExcelBanter - Complex AND OR Formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Complex AND OR Formula (https://www.excelbanter.com/excel-worksheet-functions/265317-complex-formula.html)

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

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

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

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

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

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