Perform different calculations based on cell content

[groundhog]

I've looked around and could not come up with a good idea of how to accomplish what I would like to happen. Basically, I have about half a dozen different simple calculations that need to be done in the same cell, and what the calculations are depend on certain partial text that needs to be checked.

There will be dozens if not hundreds of values so because IF doesn't work with wildcards, I'm out of luck. And nesting that name IF formulas seems silly. What I'm looking for is similar to:

=IF(A1="Text 1",B1*C1, IF(A1="Text 2",B1*C1*D1))

But again, the cell value won't match the specific text, so the equals sign is no good. How can this be done using wildcards or SEARCH? And what if I have 6-7 of these variables to check, and then do the needed calculation for that instance?

 

[groundhog]

I thought I could let this be, but I think I need some more help. There's been a slight complication. Everything is working great as I said, but there is a quirk. There will be a separate instance, where I need a binary choice (true/false) for a partial match. That is I need one formula in C9 if text in A1 contains *XXX, and another (similar) formula in C9 in A1 does not contain *XXX. Can that be accomplished with an IF/OR formula or do I need another VBA function?

 

[Peter_SSs]

I need one formula in C9 if text in A1 contains *XXX, and another (similar) formula in C9 in A1 does not contain *XXX. Can that be accomplished with an IF/OR formula or do I need another VBA function?

You don't need vba.

If the "XXX" must match the case as written in the formula then
=IF(ISNUMBER(FIND("XXX",A1)),first_formula,second_formula)

If upper/lower case does not matter then
=IF(ISNUMBER(SEARCH("XXX",A1)),first_formula,second_formula)

Further, if first_formula and second_formula, which are similar apparently, are long then there may be a more efficient way to write the above choice. If that is the case then can you post first_formula and second_formula so we can at least consider that option?

 

[groundhog]

Further, if first_formula and second_formula, which are similar apparently, are long then there may be a more efficient way to write the above choice. If that is the case then can you post first_formula and second_formula so we can at least consider that option?

Thanks, Peter. No, the formula isn't long or complicated.

If A1 contains XXX, it will be:

=MAX(-A7+B7)*100*G9

But if A1 does not contain XXX, then it should be:

=MAX(-A7+B7)*200*G9

 

[Peter_SSs]

If A1 contains XXX, it will be:

=MAX(-A7+B7)*100*G9

But if A1 does not contain XXX, then it should be:

=MAX(-A7+B7)*200*G9

Note that MAX() in those formulas does nothing so it could be eliminated. This would do exactly the same thing.

=(-A7+B7)*100*G9


Also instead of repeating the whole formula, it could be written like this since the XXX only influences the 100 or 200 choice.

=(B7-A7)*IF(ISNUMBER(FIND("XXX",A1)),100,200)*G9

 

[groundhog]

The reason I used MAX, in my understanding, is it can be used to subtract a smaller number from a larger number. In my case that's not always A and B. It works for what I require, maybe it's not straightforward, but would *IF(ISNUMBER(FIND("XXX",A1)),100,200)*G9 work if I keep that MAX?

 

[Peter_SSs]

Yes it would work, but I repeat, your MAX function is doing nothing in that particular formula. From the Help on the MAX function

Description
Returns the largest value in a set of values.

With your formula
=MAX(-A7+B7)*100*G9

What happens first is the the calculation inside the parentheses is done. That is, -A7 + B7 is calculated and will result in a single number. So your formula becomes
=MAX(single number)*100*G9
The MAX of a single number will always be that single number.

If still not convinced try this in a fresh worksheet.
In C7: =MAX(-A7 + B7)
In D7: =-A7 + B7
Now try as many different numbers as you like in A7 and B7 and see if C7 and D7 ever show different numbers to each other.

 

[groundhog]

Thank you, Peter! That work flawlessly, as expected!

 

[Peter_SSs]

Thank you, Peter! That work flawlessly, as expected!

You're welcome.