| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculation based on certain criteria
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculation based on certain criteria
Try
=IF(M2="Travel",S2/117.5*100,S2/105*100) Mike "Sarah (OGI)" wrote: 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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculation based on certain criteria
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. "Mike H" wrote: Try =IF(M2="Travel",S2/117.5*100,S2/105*100) Mike "Sarah (OGI)" wrote: 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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculation based on certain criteria
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 "Sarah (OGI)" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculation based on certain criteria
=IF(ISERROR(SEARCH("travel",M2)),S2/105*100,S2/117.5*100)
"Sarah (OGI)" wrote: 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. "Mike H" wrote: Try =IF(M2="Travel",S2/117.5*100,S2/105*100) Mike "Sarah (OGI)" wrote: 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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculation based on certain criteria
Or =IF(ISERROR(SEARCH("travel",M2)),105,117.5)*S2/100
-- David Biddulph "Mike H" wrote in message ... =IF(ISERROR(SEARCH("travel",M2)),S2/105*100,S2/117.5*100) "Sarah (OGI)" wrote: 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. "Mike H" wrote: Try =IF(M2="Travel",S2/117.5*100,S2/105*100) Mike "Sarah (OGI)" wrote: 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? |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculation based on certain criteria
=IF(COUNTIF(M2,"*travel*"),S2/117.5*100,S2/105*100)
"Sarah (OGI)" wrote: 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? |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Calculation based on certain criteria
This worked a treat, many thanks!!
"Mike H" wrote: =IF(ISERROR(SEARCH("travel",M2)),S2/105*100,S2/117.5*100) "Sarah (OGI)" wrote: 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. "Mike H" wrote: Try =IF(M2="Travel",S2/117.5*100,S2/105*100) Mike "Sarah (OGI)" wrote: 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Countif with multiple criteria (incl. calculation) | Excel Worksheet Functions | |||
| Ignore calculation when criteria exceeds 12 | Excel Discussion (Misc queries) | |||
| Calculation based on date | Excel Discussion (Misc queries) | |||
| calculation based on meeting two criteria | Excel Discussion (Misc queries) | |||
| Calculation based on a condition | Excel Worksheet Functions | |||
Linear Mode
