# Perform different calculations based on cell content

#### groundhog

##### New Member
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:

Code:
``=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

##### New Member
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?

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Peter_SSs

##### MrExcel MVP, Moderator
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

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

##### MrExcel MVP, Moderator
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

##### New Member

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

##### MrExcel MVP, Moderator
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.
=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.

Last edited:

#### groundhog

##### New Member
Thank you, Peter! That work flawlessly, as expected!

#### Peter_SSs

##### MrExcel MVP, Moderator
Thank you, Peter! That work flawlessly, as expected!
You're welcome.

Replies
4
Views
140
Replies
5
Views
193
Replies
10
Views
162
Replies
1
Views
150
Replies
3
Views
231

1,128,020
Messages
5,628,181
Members
416,297
Latest member
Kara Payne

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back