Function Using If..Then Select Case...

E

Excelster

New Member
Joined
Jan 18, 2008
Messages
3
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Hi - I'm a new member to this forum. I'm trying to come up with a UDF that will take two inputs and based on two sets of four ranges look up the intersecting value. Specifically, I have four sets of probabilities and four sets of severities (impacts), and for each combination there is a defined Action. Picture a matrix with the 4 probability ranges on the Y Axis and the 4 Impact ranges on the X axis. For example, given an Impact value of 15 and a probability of 50%, the result should be "Action 3" (see matrix). I think I'm close but I just cannot get this function to work. Any help is much appreciated. Thanks!!

Here's the matrix and the code I have so far:

>70% |Action 1 |Action 3| Action 4| Action 4
>40 to <70% |Action 1| Action 2 |Action 3 |Action 4
>10 to <40% |Action 1| Action 2| Action 3| Action 3


0 to <10% |Action 1| Action 1| Action 2| Action 2
0 to ≤7 | >7 to ≤14 | >14 to ≤24 | >24​

Function Action(Num_Prob As Variant, Num_Impact As Variant)
If Num_Impact <= 7 Then
Select Case Num_Prob
Case Is < 0.1
Action = "Action_One"
Case Is >= 0.1 < 0.7
Action = "Action_One"
Case Is >= 0.7
Action = "Action_One"
End Select
ElseIf Num_Impact > 7 <= 14 Then
Select Case Num_Prob
Case Is < 0.1
Action = "Action_One"
Case Is >= 0.1 < 0.7
Action = "Monitor"
Case Is >= 0.7
Action = "Action_Three"
End Select
ElseIf Num_Impact > 14 <= 24 Then
Select Case Num_Prob
Case Is < 0.1
Action = "Action_Two"
Case Is >= 0.1 < 0.7
Action = "Action_Three"
Case Is >= 0.7
Action = "Action_Four"
End Select
ElseIf Num_Impact > 24 Then
Select Case Num_Prob
Case Is < 0.1
Action = "Action_Two"
Case Is >= 0.1 < 0.4
Action = "Action_Three"
Case Is >= 0.4 < 0.7
Action = "Action_Four"
Case Is >= 0.7
Action = "Action_Four"
End Select
End If
End Function
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
Why not drop the If?

Try this, which worked on your example.
Code: 
Function Action(Num_Prob As Variant, Num_Impact As Variant)
    
    Select Case Num_Impact
        Case Is <= 7
            Select Case Num_Prob
                Case Is < 0.1
                    Action = "Action_One"
                Case 0.1 To 0.7
                    Action = "Action_One"
                Case Is >= 0.7
                    Action = "Action_One"
            End Select
        Case 7 To 14
            Select Case Num_Prob
                Case Is < 0.1
                    Action = "Action_One"
                Case 0.1 To 0.7
                    Action = "Monitor"
                Case Is >= 0.7
                    Action = "Action_Three"
            End Select
        Case 14 To 24
                Select Case Num_Prob
                    Case Is < 0.1
                        Action = "Action_Two"
                    Case 0.1 To 0.7
                         Action = "Action_Three"
                    Case Is >= 0.7
                        Action = "Action_Four"
                End Select
            Case Is > 24
                Select Case Num_Prob
                    Case Is < 0.1
                        Action = "Action_Two"
                    Case 0.1 To 0.4
                        Action = "Action_Three"
                    Case 0.4 To 0.7
                        Action = "Action_Four"
                    Case Is >= 0.7
                Action = "Action_Four"
        End Select
    End Select
End Function
 
Upvote 0
Thanks for the quick response !!
I tried that but am getting error msg "That function is not valid" ?? I'm using Excel 2007 would that make a difference?
Also - In your example, you use "Case 7 To 14" and "Case 14 To 24" but won;t 14 cause a probem? How can I change this to have the upper ranges be less than or equal to eg 7 <= 14; 14 <= 24 .
Thanks again.
 
Upvote 0
Also - In your example, you use "Case 7 To 14" and "Case 14 To 24" but won;t 14 cause a probem? How can I change this to have the upper ranges be less than or equal to eg 7 <= 14; 14 <= 24 .
Thanks again.
Click to expand...

You don't have to change it because it already does that. 14 won't cause a problem because it will execute the first set of statements that is TRUE.

So in Case 7 to 14, it would do this if it was 14, and it wouldn't get to the next set, so in effect if it got to the next section, it would look for greater than 14 and less than or equal to 24, exactly what you want.
 
Upvote 0
Where do you have the code?

I'm afraid I don't have 2007 and I've never seen that error.

The code should reside in a standard module.

In versions prior to 2007 you would open the VBE(Alt+F11) and then goto Insert>Module.
 
Upvote 0
You guys are brilliant. Many thanks! I got Noire's code working (had extra space - duh). Then I still had issues with the Impact Case 7 to 24 - the Prob Case .1 to .7 was not correctly returning "Action 4" - kept getting "Action 3". But now that I get Hotpepper's explanation, I realized I just had to reverse how I ordered the Prob Cases... ie: within each Impact Case I list the Prob Cases from high to low like this:

Case 14 To 24
Select Case Num_Prob
Case Is >= 0.7
Risk_Action = "Action 4"
Case 0.1 To 0.7
Risk_Action = "Action 3"
Case Is < 0.1
Risk_Action = "Action 2"
End Select

Now it works!! Boy - when you point out the answer it seems so obvious - but I was struggling. Thanks again!
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Weighted average with SUMIFS
Replies
3
Views
356
AhoyNC
A
R
  • Question
Input box and 7 msgboxes to correspond to the target range.
Replies
1
Views
170
rlv01
R
F
  • Question
Counting when record falls within a dynamically updating date range, additional matching criteria
Replies
1
Views
356
awoohaw
awoohaw
F
  • Question
Count # of values that fall within a certain date range
Replies
1
Views
384
RobP
R
D
  • Question
Issue with EntireRow.Delete
2
Replies
11
Views
814
RoryA
RoryA

Forum statistics

Threads
1,215,107
Messages
6,123,126
Members
449,097
Latest member
mlckr

Share this page

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