Auto populate function

[chris007]

Hello, again people.
could someone please assist me with a formula.
my problem is that I have cells that contain words like "High (24)" and "Moderate (15)" etc. currently I am inserting a score based on a single word.
What do I need to do to insert a value based on Alpha numeric as indicated above.

this is the curent formula i have
=IF(CT4="Low",1,IF(CT4="Minor ",3,IF(CT4="Moderate",10,IF(CT4="Major",30,IF(CT4="Serious",100,IF(CT4="Significant",300,IF(CT4="Catastrophic",1000,IF(CT4="",0))))))))

So instead of =If(CT4="low (10),1,If .............

thank you
regards
Chris007

 

[CWatts]

It seems you left some stuff out of your explanation about what you really want so I made an assumption with this formula:

=IF(A1="","",INDEX({1,3,10,30,100,300,1000},MATCH(LEFT(A1,FIND(" ",A1,1)-1),{"Low","Minor","Moderate","Major","Serious","Significant","Catastrophic"},0)))

Basically it extracts the word from the "Moderate (15)" string, and searches for the word in the list, and then matches the appropriate number with it. You didn't put any number for "High" so you'll have to add the word, and the value.

 

[chris007]

CWATTs,
Thank you, that worked perfectly, I appreciate your help.

I have actually been thinking about the process and what i needed after having the formula work and I actually need the formula to look up info from the table below and then place a score from 1, 3, 10, 30, 100, 300, 1000.
The scores are governed by the Consequence.

<TABLE style="WIDTH: 583pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=776 border=0><COLGROUP><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" span=2 width=85><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl95 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 118pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 28.5pt; BACKGROUND-COLOR: #d9d9d9" width=157 height=38 rowSpan=2>Likelihood</TD><TD class=xl97 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; WIDTH: 465pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #d9d9d9" width=619 colSpan=7>Consequence</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl83 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #fabf8f" width=96 height=18>Low</TD><TD class=xl83 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 64pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #fabf8f" width=85>Minor </TD><TD class=xl83 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 64pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #fabf8f" width=85>Moderate</TD><TD class=xl83 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #fabf8f" width=87>Major</TD><TD class=xl84 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 73pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #fabf8f" width=97>Serious</TD><TD class=xl92 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 68pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #fabf8f" width=90>Significant</TD><TD class=xl92 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #fabf8f" width=79>Catastrophic</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl85 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 118pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=157 height=34>Almost certain</TD><TD class=xl74 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #0070c0" width=96>High (27)</TD><TD class=xl77 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 64pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #0070c0" width=85>High (28)</TD><TD class=xl77 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 64pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #0070c0" width=85>High (29)</TD><TD class=xl78 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #dd0806" width=87>Extreme (39)</TD><TD class=xl78 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 73pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #dd0806" width=97>Extreme (40)</TD><TD class=xl78 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 68pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #dd0806" width=90>Extreme (41)</TD><TD class=xl78 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #dd0806" width=79>Extreme (42)</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl85 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 118pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=157 height=34>Likely</TD><TD class=xl73 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #00b050" width=96>Moderate (16)</TD><TD class=xl76 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 64pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #00b050" width=85>Moderate (17)</TD><TD class=xl77 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 64pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #0070c0" width=85>High (26)</TD><TD class=xl78 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #dd0806" width=87>Extreme (35)</TD><TD class=xl78 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 73pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #dd0806" width=97>Extreme (36)</TD><TD class=xl78 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 68pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #dd0806" width=90>Extreme (37)</TD><TD class=xl78 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #dd0806" width=79>Extreme (38)</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD class=xl85 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 118pt; BORDER-BOTTOM: black 1pt solid; HEIGHT: 19.5pt; BACKGROUND-COLOR: transparent" width=157 height=26>Possible</TD><TD class=xl70 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #ffff66" width=96>Low (8)</TD><TD class=xl76 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 64pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #00b050" width=85>Moderate (14)</TD><TD class=xl76 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 64pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #00b050" width=85>Moderate (15)</TD><TD class=xl77 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #0070c0" width=87>High (25)</TD><TD class=xl88 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 73pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #dd0806" width=97>Extreme (32)</TD><TD class=xl88 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 68pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #dd0806" width=90>Extreme (33)</TD><TD class=xl88 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #dd0806" width=79>Extreme (34)</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl86 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 1pt solid; WIDTH: 118pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" width=157 height=22>Unlikely</TD><TD class=xl71 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffff66" width=96>Low (6)</TD><TD class=xl75 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 64pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffff66" width=85>Low (7)</TD><TD class=xl79 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 64pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #00b050" width=85>Moderate (13)</TD><TD class=xl81 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #0070c0" width=87>High (22)</TD><TD class=xl77 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 73pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #0070c0" width=97>High (23)</TD><TD class=xl77 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 68pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #0070c0" width=90>High (24)</TD><TD class=xl78 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #dd0806" width=79>Extreme (31)</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD class=xl87 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 118pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" width=157 height=22>Rare</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff66" width=96>Low (4)</TD><TD class=xl72 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 64pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff66" width=85>Low (5)</TD><TD class=xl80 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 64pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #00b050" width=85>Moderate (11)</TD><TD class=xl94 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #00b050" width=87>Moderate (12)</TD><TD class=xl82 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: windowtext; BORDER-LEFT: #d4d0c8; WIDTH: 73pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #0070c0" width=97>High (20)</TD><TD class=xl93 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: black; BORDER-LEFT: black 1pt solid; WIDTH: 68pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #0070c0" width=90>High (21)</TD><TD class=xl78 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #dd0806" width=79>Extreme (30)</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl87 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 118pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=157 height=21>Very Rare</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff66" width=96>Low (1)</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff66" width=85>Low (2)</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 64pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #ffff66" width=85>Low (3)</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #00b050" width=87>Moderate (9)</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 73pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #00b050" width=97>Moderate (10)</TD><TD class=xl91 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 68pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #0070c0" width=90>High (18)</TD><TD class=xl77 style="BORDER-RIGHT: black 1pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 59pt; BORDER-BOTTOM: black 1pt solid; BACKGROUND-COLOR: #0070c0" width=79>High (19)</TD></TR></TBODY></TABLE>

So
Low = High (27), Moderate (16), Low (8), Low (6), Low (4), Low (1)
Minor = High (28), Moderate (17), Moderate (14), Low (7), Low (5), Low (2)
Moderate = High (29), High (26), Moderate (15), Moderate (13), Moderate (11), Low (3)

Any further help would be appreciated

Regards
Chris007

 

[CWatts]

So if I understand, you will have something that says Extreme (32) in cell A1 and you need another cell to return Serious and another to return Possible?

 

[chris007]

CWatts,
Thank you for your reply, hopefully I can clarify.

What happens is that when cell CU4 is contains a likleyhood eg "Almost Certain" and cell BZ4 contains the highest value from "Low to catastrophic" cell CV4 looks up the table and based on the table returns the MATCHED criteria eg "Almost certain" and "Moderate" look up table returns "High (29).

What I would like to happen in this instance is that if "high (29)" is in cell CV4 then the value equivilant to "Moderate" in this case 10 is placed in Cell CY4.

Hope this makes sense.
this is a copy of the formula in cell CV4 to return the Risk value in the CV4 Cell. =IF(CT4<>"",INDEX('Risk Criteria'!$D$63:$J$68,MATCH(CU4,'Risk Criteria'!$C$63:$C$68,0),MATCH(CT4,'Risk Criteria'!$D$62:$J$62,0)),"")

thanks Again for your help
regards
Chris007

 

[CWatts]

Hope this makes sense.

Nope it doesn't.

I understand that you're using Moderate/Almost Certain to get your High (29). What i"m not understanding is what criteria you are using to get from High (29) to Moderate (10).

Can you clarify that part?

 

[chris007]

Cwatts,
Sorry very confusing I know.

Lets see if I can explain it better, fingers crossed i have been working on this all day.

in the spreadsheet i have and lets assume for this exercise they are "A4 to G4". in each of these cells I am have a select function of Low to Catastrophic depending on the Risk. for each selection if it is Low then the next column has a formula that looks for the word and then assigns a number 1 to 7, in cell H4 it looks for the highest number in the row looks up a table and the corrisponding number will represent a word Low, Minor, Moderate etc to Catastrophic. in cell I4 i then select Very rare, Rare, Likely, etc. in cell J4 the formula then looks at the Likelyhood word and then matches the word with the Consequence I.E Low, minor, moderate etc as shown by the table in my previous post. so if you were to look at the table you can see that "high (29)" is in the row of Low and under the column of moderate. Hence any outcome under the column of low I need low, any outcome under column Minor is minor etc.

may be I can PM you the spreadsheet if this would help?

Thanks
Chris007

 

[chris007]

So I guess if High (29) comes up because it is the column Moderate and Moderate I want the score of 10 to be placed in a following cell because I will need this cell to then multiply another figure. the column in the table is the important as your formula that did work ie 1, 10, 30, 100, etc they represent the numbers associated with the column titles.

hope this helps some

 

[CWatts]

I'm not understanding. You have cells A4:G4, and then there's a next column? Moderate to Moderate? I'm afraid I can't understand what you're trying to do so I have to duck out of this one. Sorry. Hope someone else can understand and can help.

 

[chris007]

Cwatts,
Thanks, I am hoping you will percever for me I have the following which may help

<TABLE style="WIDTH: 546pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=735><COLGROUP><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" span=7 width=35><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" span=3 width=35><TBODY><TR style="HEIGHT: 20.25pt; mso-height-source: userset" height=27><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #92d050; WIDTH: 520pt; HEIGHT: 20.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl82 height=27 width=700 colSpan=20>Residual Risk Rating (post control implementation) (RRR)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #974706; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl84 width=35></TD></TR><TR style="HEIGHT: 204pt; mso-height-source: userset" height=272><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; HEIGHT: 204pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 height=272 width=35>Health & Safety impact</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35>Environmental Impact</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35>Social & Cultural Impact</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35>Reputational Impact</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35>Legal impact</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35>Financial impact</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35>Reputational / Media </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35>Manageability</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff99; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73 width=35>Highest Consequence</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #002060; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 width=35>Highest Consequence</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c00000; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl76 width=35>Likelihood Fatcor</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #002060; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl75 width=35>Highest Risk Level</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #974706; WIDTH: 26pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl77 width=35>Severity Factor</TD></TR><TR style="HEIGHT: 24pt; mso-height-source: userset" height=32><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; HEIGHT: 24pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 height=32 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #006600; WIDTH: 26pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl80 width=35></TD></TR><TR style="HEIGHT: 79.5pt" height=106><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 26pt; HEIGHT: 79.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 height=106 width=35> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 26pt; FONT-FAMILY: Arial; BACKGROUND: #d8e4bc; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl74 width=35>Minor</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35>Significant</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 26pt; FONT-FAMILY: Arial; BACKGROUND: #e26b0a; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl74 width=35>Significant</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl74 width=35>Possible</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 26pt; FONT-FAMILY: Arial; BACKGROUND: red; COLOR: windowtext; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" class=xl74 width=35>Extreme (33)</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 26pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl78 width=35>30</TD></TR></TBODY></TABLE>

The formula Next to the word Minor is
=IF(BS4="Catastrophic",7,IF(BS4="Significant",6,IF(BS4="Serious",5,IF(BS4="Major",4,IF(BS4="Moderate",3,IF(BS4="Minor",2,IF(BS4="Low",1,0)))))))

The formula for highest consequence numeric is
=MAX(BP4,BR4,BT4,BV4,BX4,BZ4,CB4,CD4)

The formula for Highest Consequence txt is
=IF(CE4=7,"Catastrophic",IF(CE4=6,"Significant",IF(CE4=5,"Serious",IF(CE4=4,"Major",IF(CE4=3,"Moderate",IF(CE4=2,"Minor ",IF(CE4=1,"Low",IF(CE4=0,""))))))))

The formula for selecting the Highest Risk Level is
=IF(CF4<>"",INDEX('Risk Criteria'!$D$63:$J$68,MATCH(CG4,'Risk Criteria'!$C$63:$C$68,0),MATCH(CF4,'Risk Criteria'!$D$62:$J$62,0)),"")

The highest risk level comes from the following table
<TABLE style="WIDTH: 630pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=838><COLGROUP><COL style="WIDTH: 118pt; mso-width-source: userset; mso-width-alt: 5741" width=157><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" span=2 width=85><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 68pt; mso-width-source: userset; mso-width-alt: 3291" width=90><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><COL style="WIDTH: 47pt" width=62><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d9d9d9; WIDTH: 118pt; HEIGHT: 28.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl102 height=38 rowSpan=2 width=157>Likelihood</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; WIDTH: 465pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl104 width=619 colSpan=7>Consequence</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=62></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fabf8f; WIDTH: 72pt; HEIGHT: 13.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl90 height=18 width=96>Low</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fabf8f; WIDTH: 64pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl90 width=85>Minor </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fabf8f; WIDTH: 64pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl90 width=85>Moderate</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fabf8f; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl90 width=87>Major</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fabf8f; WIDTH: 73pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl91 width=97>Serious</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fabf8f; WIDTH: 68pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl99 width=90>Significant</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fabf8f; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl99 width=79>Catastrophic</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl75 width=62>N/A</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 118pt; HEIGHT: 25.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl92 height=34 width=157>Almost certain</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 72pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl81 width=96>High (27)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 64pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl84 width=85>High (28)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 64pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl84 width=85>High (29)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dd0806; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl85 width=87>Extreme (39)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dd0806; WIDTH: 73pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl85 width=97>Extreme (40)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dd0806; WIDTH: 68pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl85 width=90>Extreme (41)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dd0806; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl85 width=79>Extreme (42)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl73 width=62>N/A</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 118pt; HEIGHT: 25.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl92 height=34 width=157>Likely</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #00b050; WIDTH: 72pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl80 width=96>Moderate (16)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #00b050; WIDTH: 64pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl83 width=85>Moderate (17)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 64pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl84 width=85>High (26)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dd0806; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl85 width=87>Extreme (35)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dd0806; WIDTH: 73pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl85 width=97>Extreme (36)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dd0806; WIDTH: 68pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl85 width=90>Extreme (37)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dd0806; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl85 width=79>Extreme (38)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl74 width=62>N/A</TD></TR><TR style="HEIGHT: 19.5pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 118pt; HEIGHT: 19.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl92 height=26 width=157>Possible</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff66; WIDTH: 72pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl77 width=96>Low (8)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #00b050; WIDTH: 64pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl83 width=85>Moderate (14)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #00b050; WIDTH: 64pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl83 width=85>Moderate (15)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl84 width=87>High (25)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dd0806; WIDTH: 73pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl95 width=97>Extreme (32)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dd0806; WIDTH: 68pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl95 width=90>Extreme (33)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dd0806; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl95 width=79>Extreme (34)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl73 width=62>N/A</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 118pt; HEIGHT: 16.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl93 height=22 width=157>Unlikely</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff66; WIDTH: 72pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl78 width=96>Low (6)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff66; WIDTH: 64pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl82 width=85>Low (7)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #00b050; WIDTH: 64pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl86 width=85>Moderate (13)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 65pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl88 width=87>High (22)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 73pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl84 width=97>High (23)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 68pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl84 width=90>High (24)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dd0806; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl85 width=79>Extreme (31)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl76 width=62>N/A</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 118pt; HEIGHT: 16.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl94 height=22 width=157>Rare</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff66; WIDTH: 72pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl79 width=96>Low (4)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffff66; WIDTH: 64pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl79 width=85>Low (5)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #00b050; WIDTH: 64pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl87 width=85>Moderate (11)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #00b050; WIDTH: 65pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl101 width=87>Moderate (12)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 73pt; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl89 width=97>High (20)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: black 1pt solid; BACKGROUND-COLOR: #0070c0; WIDTH: 68pt; BORDER-TOP: black; BORDER-RIGHT: #f0f0f0" class=xl100 width=90>High (21)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dd0806; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl85 width=79>Extreme (30)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl74 width=62>N/A</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 118pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl94 height=21 width=157>Very Rare</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff66; WIDTH: 72pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl96 width=96>Low (1)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff66; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl96 width=85>Low (2)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #ffff66; WIDTH: 64pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl96 width=85>Low (3)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00b050; WIDTH: 65pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl97 width=87>Moderate (9)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #00b050; WIDTH: 73pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl97 width=97>Moderate (10)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #0070c0; WIDTH: 68pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl98 width=90>High (18)</TD><TD style="BORDER-BOTTOM: black 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #0070c0; WIDTH: 59pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl84 width=79>High (19)</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 47pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl74 width=62>N/A</TD></TR></TBODY></TABLE>

at the top of each column in this table the words Low, Minor, Moderate, Major, Serious, Significant, Catistrophic.

the "highest Risk Level" will fall within one of the above columns in this case on the main table "Extreme (33)" then the value of the column where extreme (33) is located whithin the above table is column "Significant" and would = 300. 300 would then be inserted into the last column of the main spreadsheet currently showing 30. this is based on Low = 1, Minor = 3, Moderate = 10, Major =30, Serious = 100, Significant = 300 and Catastrophic = 1000 i need the be inserted into "Severity FActor" cell of the main table (the first table i Pasted and it now is showing 30)


I do hope that you could help or someone is able to HELP PLEASE

Thanks
Regards
Chris007