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?

 

[JoeMo]

Have you considered doing this with VBA?

 

[Akuini]

How about using a User Defined Function (UDF)?
You create your own function, and use it like regular function.
In the code you can use If statement + Like operator (using wildcard or a specific character) to specify what pattern should do what calculation.

Here's an example:
Put this code in a code module:

Function groundho(dCell As Range)
Dim x As Long
x = dCell.Row
    If dCell.Value Like "*-1" Then
        groundho = Cells(x, "B") + Cells(x, "C")
    ElseIf dCell.Value Like "*-2" Then
        groundho = Cells(x, "B") + Cells(x, "C") + Cells(x, "D")
    End If

End Function

Formula in F1 (drag down): =groundho(A1)

Excel 2013
	A	B	C	D	E	F
1	asd-1	1	2	3		3
2	FHasd-1	1	2	3		3
3	Gff jf -2	1	2	3		6
Sheet1

<p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />

 

[Eric W]

Welcome to the forum.

You could use formulas to achieve your goal, but you might need to add some more detail. Consider this layout:

Excel 2012

	A	B	C	D	E	F	G
xxdefyy	Code	Formula
abc
def
ghi
jkl

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]43[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3.141593[/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]A2[/TH]
[TD="align: left"]=LOOKUP(2^16,SEARCH($F$2:$F$5,A1),$G$2:$G$5)[/TD]
[/TR]
[TR]
[TH]G2[/TH]
[TD="align: left"]=B1*C1[/TD]
[/TR]
[TR]
[TH]G3[/TH]
[TD="align: left"]=B1*C1*D1[/TD]
[/TR]
[TR]
[TH]G4[/TH]
[TD="align: left"]=42+B1[/TD]
[/TR]
[TR]
[TH]G5[/TH]
[TD="align: left"]=PI()*B1^2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



You could build a table like I did in F:G. The F values are your codes, which do not need to be a full match. The G values are the formulas you want to do. So in this example, the formula in A2 sees that "def" is found in A1, then it gets the corresponding value from the table, which is B1*C1*D1, which works out to 6. I also considered putting an index in G, then you could pass that to a CHOOSE and put the formulas within your A2 formula. There are many options. You could even embed that whole table within the A2 formula, but it would be awkward and hard to maintain, like you suppose. Better to make a table somewhere, which you can hide if you want, and change if you need to.

Maybe something like this would work for you. Let us know.

 

[Akuini]

Sorry, the UDF in post #3 is flawed, because if you change a value of e.g B1 then the result doesn't change.
So we need to include all cells that will be used in the calculations as argument in the Function, in this example there are 3 cells, like this:

Function groundho(dCell As Range, c1 As Range, c2 As Range, c3 As Range)
Dim x As Long
x = dCell.Row
    If dCell.Value Like "*-1" Then
        groundho = c1 + c2
    ElseIf dCell.Value Like "*-2" Then
        groundho = c1 + c2 + c3
    End If

End Function

And the formula in F1: =groundho(A1,B1,C1,D1)

 

[groundhog]

Thank you, I will try implementing these and report back. Meanwhile, maybe some more information could be helpful in narrowing down a solution:

In A1 there is a drop-down list of titles and in B1, C1, D1 are specific numbers. What I'm hoping to have is have calculations done based on what's selected in A1. So in some instances I need to multiply all three, sometimes only two, sometimes I need to divide by D1, and so on. I feel like Akuini's function might do the job, but if anyone else has ideas, please share. This is already very helpful, thank you!

 

[groundhog]

And I think Eric's solution should work too, I'm just still trying to grasp how it works and it's early

 

[groundhog]

I just wanted to thank everyone for their help, it was spot on! Just what I needed. I ended up using VBA as suggested and slightly modified and extrapolated Akuini's sample. It works flawlessly for what's needed! Can't thank you enough for this simple yet powerful code.

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.

 

[Peter_SSs]

I see that you have chosen to use vba and that is fine but if I have understood correctly, you can do it directly with a single formula if you want.
E1 if you have the texts of interest listed in the sheet (easier to maintain) or F1 if you want to build the texts into the formula.
You can just add more texts to the column H range or the array in the formula and more sections at the end where I have coloured them.
You may need/want to wrap the whole formula in an IFERROR() in cae none of the text values are found in the cell.

Excel Workbook
	A	B	C	D	E	F	G	H
1	This cell has text 2 in it	1	2	3	6	6		Text 1
2								Text 2
3								Text 3
Different Calcs