A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Complex AND OR Formula



 
 
Thread Tools Display Modes
  #1  
Old June 5th, 2010, 10:07 PM posted to microsoft.public.excel.worksheet.functions
Colin Hayes
external usenet poster
 
Posts: 313
Default 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
  #3  
Old June 5th, 2010, 10:42 PM posted to microsoft.public.excel.worksheet.functions
David Biddulph
external usenet poster
 
Posts: 8,714
Default 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  
Old June 5th, 2010, 10:47 PM posted to microsoft.public.excel.worksheet.functions
Colin Hayes
external usenet poster
 
Posts: 313
Default 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  
Old June 5th, 2010, 11:02 PM posted to microsoft.public.excel.worksheet.functions
Colin Hayes
external usenet poster
 
Posts: 313
Default 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  
Old June 6th, 2010, 03:02 AM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old June 6th, 2010, 12:51 PM posted to microsoft.public.excel.worksheet.functions
Stan Brown
external usenet poster
 
Posts: 536
Default 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  
Old June 6th, 2010, 03:35 PM posted to microsoft.public.excel.worksheet.functions
Colin Hayes
external usenet poster
 
Posts: 313
Default 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  
Old June 6th, 2010, 05:05 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.