Jump to content
The Christmas Logo Competition is back. See thread in Sheffield Discussions for details ×

25 Combination 3 Results (If/And/Or)? excel formula

Recommended Posts

Hi there,

 

I have 2 columns as follows:

 

Column 1

Improbable
Improbable
Improbable
Improbable
Improbable
Remote
Remote
Remote
Remote
Remote
Possible
Possible
Possible
Possible
Possible
Probable
Probable
Probable
Probable
Probable
Certainty
Certainty
Certainty
Certainty
Certainty

 

And Column2

None
Negligible
Minor
Major
Significant
None
Negligible
Minor
Major
Significant
None
Negligible
Minor
Major
Significant
None
Negligible
Minor
Major
Significant
None
Negligible
Minor
Major
Significant

 

Equals Column3

1
1
1
1
1
1
1
1
2
2
1
1
2
2
3
1
2
2
3
3
1
2
3
3
3

 

I hope it makes sense, but for example, if we take:

Improbable & None = 1; 

Remote & Major = 2;

Possible & Significant = 3;

 

What I'm trying to achieve is a RAG outcome, so the value of C1 & You've gotta cook like you don't need the elixir! Plan like you've never failed! Attack like no one is  watching! It's gotta come from the heart if you wanna win! Neo x   would equate to either, Red, Amber or Green

 

Any help greatly appreciated.

 

Many thanks

Share this post


Link to post
Share on other sites

Does this site help?

 

https://www.techno-pm.com/2017/07/rag-status-template-excel.html

Share this post


Link to post
Share on other sites

My go-to site for Excel is https://www.mrexcel.com/  I used to use excel a lot but because of my background I found it easier to write my own functions than use complicated formulas.  From the toolbar, record  a macro then stop so you have an empty macro. I called mine howScary and assigned it key control-h.  Those options appear when you start to record.  Then view macros  and select edit.  Delete the code there and insert 

Quote

Sub howScary()
'
' howScary Macro
'
' Keyboard Shortcut: Ctrl+h
'
Dim r As Long
Dim c As Long
Dim probability As String
Dim impact As String
With Sheet1 '
    For i = 1 To Rows.Count
    probability = Cells(i, 1).Value
    impact = Cells(i, 2).Value
    Select Case probability
      Case Is = "Certainty"
           If impact = Major Then Cells(i, 3) = "2"
           ElseIf impact = "None" Then Cells(i, 3) = "3"
           ElseIf impact = "Negligible" Then Cells(i, 3) = "1"
           End If
        Case Is = "Negligible"
           If impact = Major Then Cells(i, 3) = "2"
           ElseIf impact = "None" Then Cells(i, 3) = "3"
           ElseIf impact = "Negligible" Then Cells(i, 3) = "1"
           End If
     End Select
    Next i
End With
End Sub
 

 

This covers 2 case of Certainty and Negligible and assigns 3rd column based on the 2nd column.  I hope it makes sense. Happy to answer questions

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.