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!

Report •


#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 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 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


Ask Question