# Multiple Formulas in one cell - Help! September 26, 2010 at 13:58:34
Specs: Windows XP
 I am trying to figure out how to write a formula where I do multiple steps. The 1st step would be to subtract, then reference another column looking for 3 words and if any cell in that column contains any of the 3 words then subtract one. For example =(M5-K5)...is the first part of the formula then I want excel to reference column F looking for the words "good", "exact" or "ranch" and if any cell in column F contains those words then subtract 1 from (M5-K5). If no cell in column F contains those words then just (M5-K5). Can anyone help me? See More: Multiple Formulas in one cell - Help!

#1 September 26, 2010 at 15:04:31
 You can't put "multiple formulas" in one cell. Strictly speaking, you can't even get a formula to do "multiple things".A formula can only do one thing: return a resultNow, it may perform multiple calculations on the way to returning that result, but in the end it really only did 1 thing: returned a result.That said, you really don't want =(M5-K5) to be the first step, you actually want that to be the last step.If I understand your request correctly, you want to return M5-K5-1 if any of the three words are in Columns F and return M5-K5 if not.The IF function is designed to do a "logical test" and return one result if the test returns TRUE and an different result if the test returns FALSE.=IF(logical_test, value_if_true, value_if_false)In your case, the logical test is:Does Column F contain "good" or "exact" or "ranch"?We can determine if that is the case by "counting" the number of times each word occurs. If the count is greater than 0 for any of those words, then at least one of those words exists in Column F.The value_if_true is (M5-K5-1)The value_if_false is (M5-K5)Putting that all together we get:=IF(OR(COUNTIF(F:F,"good")>0, COUNTIF(F:F,"exact")>0,COUNTIF(F:F, "ranch" )>0),M5-K5-1,M5-K5)Note: the formula has been split into 2 lines to make it easier to read.

Report •

#2
September 26, 2010 at 15:21:26
 Wow...this works perfectly and thank you for the detailed explanation...greatly appreciated!

Report •

#3 September 26, 2010 at 16:10:09
 Glad to have been of assistance.

Report •
Related Solutions 