IF and OR Function

[winnie123]

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

 

[Bernard Liengme]

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)returns either C2+7 or C2+32Do you see that we could rewrite it as:=C2+IF(ISNUMBER(SEARCH("SEA",H2))+ISNUMBER(SEARCH("EXPORT",H2))+ISNUMBER(SEARCH("K",H2)),32,7)The 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))+ISNUMBER(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(SEARCH("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" <[email protected]> wrote in messageHello,>> 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 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

 

[winnie123]

Thank you Bernard,

It was the later formula I wanted.

Thanks for the explanation too

Best Regards

Winnie

 

[Bernard Liengme]

Glad to have helped