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?
 
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?
 
Upvote 0

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top