Perform different calculations based on cell content

groundhog

New Member
Joined
Oct 2, 2019
Messages
8
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
Joined
Oct 2, 2019
Messages
8
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?
 

Some videos you may like

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
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 2, 2019
Messages
8
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
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 2, 2019
Messages
8

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top