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

[NeoGen 10]

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

[swarfendor437 14]

Does this site help?

 

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

[NeoGen 10]

On 06/04/2020 at 20:38, swarfendor437 said:

Does this site help?

 

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

Hi swarfendor437,

 

Unfortunately not, I've not yet figured it out but have more time to look at it today, so will do that.

 

Thanks for your help

 

N

[RabM 16]

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