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?

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 result

Now, 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

laststep.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_trueis (M5-K5-1)The

value_if_falseis (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.

Wow...this works perfectly and thank you for the detailed explanation...greatly appreciated!

Glad to have been of assistance.

Ask Your Question

Weekly Poll

Do you think Monopoly should update its pieces?

Discuss in The Lounge

Poll History