Calculation based on certain criteria

[Sarah (OGI)]

If M2 contains the word "travel", then I want to perform the following
calculation on S2:
=S2/117.5*100

If M2 does not contain the word "travel", then I want to perform the
following on S2:
=S2/105*100.

What is the best way of writing this in a formula?

 

[Mike H]

Try

=IF(M2="Travel",S2/117.5*100,S2/105*100)

Mike

 

[Sarah (OGI)]

Thanks Mike, but M2 won't specifically just say "Travel", it will contain the
word "travel" if it says it at all, which is what I'm stuggling with.

 

[Ashish Mathur]

Hi,

Try this

=IF(ISNUMBER(SEARCH("travel",M2,1)),S2/117.5*100,S2/105*100)


--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Mike H]

=IF(ISERROR(SEARCH("travel",M2)),S2/105*100,S2/117.5*100)

 

[David Biddulph]

Or =IF(ISERROR(SEARCH("travel",M2)),105,117.5)*S2/100

 

[Teethless mama]

=IF(COUNTIF(M2,"*travel*"),S2/117.5*100,S2/105*100)

 

[Sarah (OGI)]

This worked a treat, many thanks!!

=IF(ISERROR(SEARCH("travel",M2)),S2/105*100,S2/117.5*100)