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

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### JoeMo

##### MrExcel MVP
Have you considered doing this with VBA?

#### Akuini

##### Well-known Member
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:

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

<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">asd-1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">FHasd-1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Gff jf -2</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">6</td></tr></tbody></table><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 />

Last edited:

#### Eric W

##### MrExcel MVP
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
ABCDEFG
1xxdefyy123CodeFormula
26abc2
3def6
4ghi43
5jkl3.141593

<tbody>
</tbody>
Sheet8

Worksheet Formulas
CellFormula
A2=LOOKUP(2^16,SEARCH(\$F\$2:\$F\$5,A1),\$G\$2:\$G\$5)
G2=B1*C1
G3=B1*C1*D1
G4=42+B1
G5=PI()*B1^2

<tbody>
</tbody>

<tbody>
</tbody>

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.

Last edited:

#### Akuini

##### Well-known Member

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:

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

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

##### New Member

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

#### groundhog

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

##### Well-known Member
You're welcome, glad to help, & thanks for the feedback.

#### Peter_SSs

##### MrExcel MVP, Moderator
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
ABCDEFGH
1This cell has text 2 in it12366Text 1
2Text 2
3Text 3
Different Calcs

Last edited:

Replies
4
Views
93
Replies
5
Views
167
Replies
10
Views
147
Replies
1
Views
144
Replies
3
Views
224

1,127,054
Messages
5,622,441
Members
415,895
Latest member
Akhilesh28

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

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