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