IF and OR Function
 

Hello,

Can I edit the formula below to include another criteria

=IF(OR(ISNUMBER(SEARCH("SEA",H2)),ISNUMBER(SEARCH( "EXPORT",H2)),ISNUMBER(SEARCH("K",H2))),C2+32,C2+7 )

The formula looks up Sea, Export or K within col H and if true will add 32
days onto the value in Col C, if not true it will just add 7 days.

I have a part number which will take longer to arrive than 32 days so I need
to include this in the calculation.

The formula needs to include a search in col D and if BCRFRMXX00002 is found
add 42 days to the value in col C regardless to the value in Col H .

Col H is the method of shipment

This would need to be the first search criteria.

I have tried

=IF(OR(ISNUMBER(SEARCH("BCRFRMXX00002",D2)),C2+42, ISNUMBER(SEARCH("SEA",H2)),ISNUMBER(SEARCH("EXPORT ",H2)),ISNUMBER(SEARCH("K",H2))),C2+32,C2+7)

Can you please advise me.

ManyThanks

Winnie

IF and OR Function
 

Your formula
=IF(OR(ISNUMBER(SEARCH("BCRFRMXX00002",D2)),C2+42, ISNUMBER(SEARCH("SEA",H2)),ISNUMBER(SEARCH("EXPORT ",H2)),ISNUMBER(SEARCH("K",H2))),C2+32,C2+7)return s either C2+7 or C2+32Do you see that we could rewrite it as:=C2+IF(ISNUMBER(SEARCH("SEA",H2))+ISNUMBER(SEAR CH("EXPORT",H2))+ISNUMBER(SEARCH("K",H2)),32,7)Th e argument ISNUMBER...+ISNUMBER.....+ISNUMBER will evaluate either to 0(Excel will treat this as FALSE) or it will evaluate to a number (Excel willtreat this as TRUE)Now let's add the other condition=C2+IF(ISNUMBER(SEARCH("SEA",H2))+ISNUMBE R(SEARCH("EXPORT",H2))+ISNUMBER(SEARCH("K",H2)),32 ,7) + ISNUMBER(SEARCH("BCRFRMXX00002",D2))*42In the last part ISNUMBER will be TRUE or FALSE and when this is use is amath operation it is treated as 0 or 1. So this will add 7 or 32 dependingon H2 AND 0 or 42 depending on D2But maybe you meant:If the D2 condition holds add 42 ELSE add 7 or 25 depending on the H2condition=C2+IF(ISNUMBER(SEARCH("BCRFRMXX00002", D2)),42,IF(ISNUMBER(SEARCH("SEA",H2))+ISNUMBER(SEA RCH("EXPORT",H2))+ISNUMBER(SEARCH("K",H2)),32, 7))Here we add 42 if the D2 condition holds OR we add a value depending on theH2 conditionbest wishes--Bernard V LiengmeMicrosoft Excel MVPhttp://people.stfx.ca/bliengmeremove caps from email"winnie123" wrote in ... Hello, Can I edit the formula below to include another criteria=IF(OR(ISNUMBER(SEARCH("SEA",H2)),ISNUMB ER(SEARCH("EXPORT",H2)),ISNUMBER(SEARCH("K",H2))), C2+32,C2+7) The formula looks up Sea, Export or K within col H and if true will add 32 days onto the value in Col C, if not true it will just add 7 days. I have a part number which will take longer to arrive than 32 days so Ineed to include this in the calculation. The formula needs to include a search in col D and if BCRFRMXX00002 isfound add 42 days to the value in col C regardless to the value in Col H . Col H is the method of shipment This would need to be the first search criteria. I have tried=IF(OR(ISNUMBER(SEARCH("BCRFRMXX00002",D2)) ,C2+42,ISNUMBER(SEARCH("SEA",H2)),ISNUMBER(SEARCH( "EXPORT",H2)),ISNUMBER(SEARCH("K",H2))),C2+32,C2+7 ) Can you please advise me. ManyThanks Winnie

IF and OR Function
 

Thank you Bernard,

It was the later formula I wanted.

Thanks for the explanation too

Best Regards

Winnie

"Bernard Liengme" wrote:

Your formula
=IF(OR(ISNUMBER(SEARCH("BCRFRMXX00002",D2)),C2+42, ISNUMBER(SEARCH("SEA",H2)),ISNUMBER(SEARCH("EXPORT ",H2)),ISNUMBER(SEARCH("K",H2))),C2+32,C2+7)return s either C2+7 or C2+32Do you see that we could rewrite it as:=C2+IF(ISNUMBER(SEARCH("SEA",H2))+ISNUMBER(SEAR CH("EXPORT",H2))+ISNUMBER(SEARCH("K",H2)),32,7)Th e argument ISNUMBER...+ISNUMBER.....+ISNUMBER will evaluate either to 0(Excel will treat this as FALSE) or it will evaluate to a number (Excel willtreat this as TRUE)Now let's add the other condition=C2+IF(ISNUMBER(SEARCH("SEA",H2))+ISNUMBE R(SEARCH("EXPORT",H2))+ISNUMBER(SEARCH("K",H2)),32 ,7) + ISNUMBER(SEARCH("BCRFRMXX00002",D2))*42In the last part ISNUMBER will be TRUE or FALSE and when this is use is amath operation it is treated as 0 or 1. So this will add 7 or 32 dependingon H2 AND 0 or 42 depending on D2But maybe you meant:If the D2 condition holds add 42 ELSE add 7 or 25 depending on the
H2condition=C2+IF(ISNUMBER(SEARCH("BCRFRMXX00002", D2)),42,IF(ISNUMBER(SEARCH("SEA",H2))+ISNUMBER(SEA RCH("EXPORT",H2))+ISNUMBER(SEARCH("K",H2)),32, 7))Here we add 42 if the D2 condition holds OR we add a value depending on theH2 conditionbest wishes--Bernard V LiengmeMicrosoft Excel MVPhttp://people.stfx.ca/bliengmeremove caps from email"winnie123" wrote in ... Hello, Can I edit the formula below to include another criteria=IF(OR(ISNUMBER(SEARCH("SEA",H2)),ISNUMB ER(SEARCH("EXPORT",H2)),ISNUMBER(SEARCH("K",H2))), C2+32,C2+7) The formula looks up Sea, Export or K within col H and if true will add 32 days onto the value in Col C, if not true it will just add 7 days. I have a part number which will take longer to arrive than 32 days so Ineed to include this in the calculation. The formula needs to include a search in col D and if BCRFRMXX00002 isfound add 42 days to the value
in col C regardless to the value in Col H . Col H is the method of shipment This would need to be the first search criteria. I have tried=IF(OR(ISNUMBER(SEARCH("BCRFRMXX00002",D2)) ,C2+42,ISNUMBER(SEARCH("SEA",H2)),ISNUMBER(SEARCH( "EXPORT",H2)),ISNUMBER(SEARCH("K",H2))),C2+32,C2+7 ) Can you please advise me. ManyThanks Winnie

IF and OR Function
 

Glad to have helped
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"winnie123" wrote in message
...
Thank you Bernard,

It was the later formula I wanted.

Thanks for the explanation too

Best Regards

Winnie

"Bernard Liengme" wrote:

Your formula

=IF(OR(ISNUMBER(SEARCH("BCRFRMXX00002",D2)),C2+42, ISNUMBER(SEARCH("SEA",H2)),ISNUMBER(SEARCH("EXPORT ",H2)),ISNUMBER(SEARCH("K",H2))),C2+32,C2+7)return s
either C2+7 or C2+32Do you see that we could rewrite it
as:=C2+IF(ISNUMBER(SEARCH("SEA",H2))+ISNUMBER(SEAR CH("EXPORT",H2))+ISNUMBER(SEARCH("K",H2)),32,7)Th e
argument ISNUMBER...+ISNUMBER.....+ISNUMBER will evaluate either to
0(Excel will treat this as FALSE) or it will evaluate to a number (Excel
willtreat this as TRUE)Now let's add the other
condition=C2+IF(ISNUMBER(SEARCH("SEA",H2))+ISNUMBE R(SEARCH("EXPORT",H2))+ISNUMBER(SEARCH("K",H2)),32 ,7)
+ ISNUMBER(SEARCH("BCRFRMXX00002",D2))*42In the last part ISNUMBER will
be TRUE or FALSE and when this is use is amath operation it is treated as
0 or 1. So this will add 7 or 32 dependingon H2 AND 0 or 42 depending on
D2But maybe you meant:If the D2 condition holds add 42 ELSE add 7 or 25
depending on the
H2condition=C2+IF(ISNUMBER(SEARCH("BCRFRMXX00002", D2)),42,IF(ISNUMBER(SEARCH("SEA",H2))+ISNUMBER(SEA RCH("EXPORT",H2))+ISNUMBER(SEARCH("K",H2)),32,
7))Here we add 42 if the D2 condition holds OR we add a value depending on
theH2 conditionbest wishes--Bernard V LiengmeMicrosoft Excel
MVPhttp://people.stfx.ca/bliengmeremove caps from email"winnie123"
wrote in
...
Hello, Can I edit the formula below to include another
criteria=IF(OR(ISNUMBER(SEARCH("SEA",H2)),ISNUMB ER(SEARCH("EXPORT",H2)),ISNUMBER(SEARCH("K",H2))), C2+32,C2+7)
The formula looks up Sea, Export or K within col H and if true will add
32 days onto the value in Col C, if not true it will just add 7 days. I
have a part number which will take longer to arrive than 32 days so Ineed
to include this in the calculation. The formula needs to include a
search in col D and if BCRFRMXX00002 isfound add 42 days to the value
in col C regardless to the value in Col H . Col H is the method of
shipment This would need to be the first search criteria. I have
tried=IF(OR(ISNUMBER(SEARCH("BCRFRMXX00002",D2)) ,C2+42,ISNUMBER(SEARCH("SEA",H2)),ISNUMBER(SEARCH( "EXPORT",H2)),ISNUMBER(SEARCH("K",H2))),C2+32,C2+7 )
Can you please advise me. ManyThanks Winnie