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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,559
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,299
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,559
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,559
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,523
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:

Forum statistics

Threads
1,089,604
Messages
5,409,239
Members
403,256
Latest member
Viq

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top