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?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,561
Office Version
365
Platform
Windows
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
Joined
Aug 18, 2015
Messages
9,313
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
Joined
Feb 1, 2016
Messages
2,561
Office Version
365
Platform
Windows
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
Joined
Oct 2, 2019
Messages
8
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
Joined
Oct 2, 2019
Messages
8
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
Joined
Oct 2, 2019
Messages
8
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
Joined
Feb 1, 2016
Messages
2,561
Office Version
365
Platform
Windows
You're welcome, glad to help, & thanks for the feedback.:)
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,650
Office Version
365
Platform
Windows
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:

Watch MrExcel Video

Forum statistics

Threads
1,090,047
Messages
5,412,050
Members
403,409
Latest member
IHRAcer

This Week's Hot Topics

Top