Formula/Function Required - Please help! :)

HayleyS · Sep 15, 2009

Hi,

Was wondering if anyone could help re: code for the following issue.

****** http-equiv="Content-Type" content="text/html; charset=utf-8">****** name="ProgId" content="Word.Document">****** name="Generator" content="Microsoft Word 12">****** name="Originator" content="Microsoft Word 12"><link rel="File-List" href="file:///C:%5CDOCUME%7E1%5CHAYLEY%7E1.STE%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_filelist.xml"><link rel="themeData" href="file:///C:%5CDOCUME%7E1%5CHAYLEY%7E1.STE%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_themedata.thmx"><link rel="colorSchemeMapping" href="file:///C:%5CDOCUME%7E1%5CHAYLEY%7E1.STE%5CLOCALS%7E1%5CTemp%5Cmsohtmlclip1%5C01%5Cclip_colorschememapping.xml"><style> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format

ther; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin:0cm; margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:12.0pt; font-family:"Times New Roman","serif"; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; font-size:10.0pt; mso-ansi-font-size:10.0pt; mso-bidi-font-size:10.0pt;} @page Section1 {size:612.0pt 792.0pt; margin:72.0pt 72.0pt 72.0pt 72.0pt; mso-header-margin:36.0pt; mso-footer-margin:36.0pt; mso-paper-source:0;} div.Section1 {page:Section1;} --> </style> [FONT=&quot]I have a Risk Assessment Table (see below):<o

></o

>[/FONT]
[FONT=&quot]<o

>
</o

>[/FONT]
<table class="MsoNormalTable" style="width: 445pt; margin-left: -0.9pt; border-collapse: collapse;" width="593" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15.75pt;"> <td style="border: 1pt solid navy; padding: 0cm 5.4pt; background: rgb(90, 90, 90) none repeat scroll 0% 0%; width: 138pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="184"> [FONT=&quot] <o

></o

>[/FONT]
</td> <td colspan="5" style="border-style: solid solid solid none; border-color: navy navy navy -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; background: rgb(90, 90, 90) none repeat scroll 0% 0%; width: 307pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="409"> [FONT=&quot]Consequences<o

></o

>[/FONT]
</td> </tr> <tr style="height: 22.5pt;"> <td rowspan="2" style="border-style: none solid double; border-color: -moz-use-text-color navy navy; border-width: medium 1pt 2.25pt; padding: 0cm 5.4pt; width: 138pt; height: 22.5pt;" valign="top" width="184"> [FONT=&quot]Likelihood<o

></o

>[/FONT]
</td> <td style="border-style: none solid none none; border-color: -moz-use-text-color navy -moz-use-text-color -moz-use-text-color; border-width: medium 1pt medium medium; padding: 0cm 5.4pt; width: 115pt; height: 22.5pt;" valign="top" width="153">

[FONT=&quot]Insignificant<o

></o

>[/FONT]

</td> <td style="border-style: none solid none none; border-color: -moz-use-text-color navy -moz-use-text-color -moz-use-text-color; border-width: medium 1pt medium medium; padding: 0cm 5.4pt; width: 48pt; height: 22.5pt;" valign="top" width="64">

[FONT=&quot]Minor<o

></o

>[/FONT]

</td> <td style="border-style: none solid none none; border-color: -moz-use-text-color navy -moz-use-text-color -moz-use-text-color; border-width: medium 1pt medium medium; padding: 0cm 5.4pt; width: 48pt; height: 22.5pt;" valign="top" width="64">

[FONT=&quot]Moderate<o

></o

>[/FONT]

</td> <td style="border-style: none solid none none; border-color: -moz-use-text-color navy -moz-use-text-color -moz-use-text-color; border-width: medium 1pt medium medium; padding: 0cm 5.4pt; width: 48pt; height: 22.5pt;" valign="top" width="64">

[FONT=&quot]Major<o

></o

>[/FONT]

</td> <td style="border-style: none solid none none; border-color: -moz-use-text-color navy -moz-use-text-color -moz-use-text-color; border-width: medium 1pt medium medium; padding: 0cm 5.4pt; width: 48pt; height: 22.5pt;" valign="top" width="64">

[FONT=&quot]Catastrophic<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15.75pt;"> <td style="border-style: none solid none none; border-color: -moz-use-text-color navy -moz-use-text-color -moz-use-text-color; border-width: medium 1pt medium medium; padding: 0cm 5.4pt; width: 115pt; height: 15.75pt;" valign="top" width="153">

[FONT=&quot]1<o

></o

>[/FONT]

</td> <td style="border-style: none solid none none; border-color: -moz-use-text-color navy -moz-use-text-color -moz-use-text-color; border-width: medium 1pt medium medium; padding: 0cm 5.4pt; width: 48pt; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]2<o

></o

>[/FONT]

</td> <td style="border-style: none solid none none; border-color: -moz-use-text-color navy -moz-use-text-color -moz-use-text-color; border-width: medium 1pt medium medium; padding: 0cm 5.4pt; width: 48pt; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]3<o

></o

>[/FONT]

</td> <td style="border-style: none solid none none; border-color: -moz-use-text-color navy -moz-use-text-color -moz-use-text-color; border-width: medium 1pt medium medium; padding: 0cm 5.4pt; width: 48pt; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]4<o

></o

>[/FONT]

</td> <td style="border-style: none solid none none; border-color: -moz-use-text-color navy -moz-use-text-color -moz-use-text-color; border-width: medium 1pt medium medium; padding: 0cm 5.4pt; width: 48pt; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]5<o

></o

>[/FONT]

</td> </tr> <tr style="height: 16.5pt;"> <td style="border-style: none solid; border-color: -moz-use-text-color navy; border-width: medium 1pt; padding: 0cm 5.4pt; width: 138pt; height: 16.5pt;" valign="top" width="184"> [FONT=&quot]A. [FONT=&quot]Almost Certain<o

></o

>[/FONT][/FONT]
</td> <td style="border-style: double solid none none; border-color: navy navy -moz-use-text-color -moz-use-text-color; border-width: 2.25pt 1pt medium medium; padding: 0cm 5.4pt; background: yellow none repeat scroll 0% 0%; width: 115pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 16.5pt;" valign="top" width="153">

[FONT=&quot]H (15)<o

></o

>[/FONT]

</td> <td style="border-style: double solid none none; border-color: navy navy -moz-use-text-color -moz-use-text-color; border-width: 2.25pt 1pt medium medium; padding: 0cm 5.4pt; background: yellow none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 16.5pt;" valign="top" width="64">

[FONT=&quot]H (10)<o

></o

>[/FONT]

</td> <td style="border-style: double solid none none; border-color: navy navy -moz-use-text-color -moz-use-text-color; border-width: 2.25pt 1pt medium medium; padding: 0cm 5.4pt; background: red none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 16.5pt;" valign="top" width="64">

[FONT=&quot]E (6)<o

></o

>[/FONT]

</td> <td style="border-style: double solid none none; border-color: navy navy -moz-use-text-color -moz-use-text-color; border-width: 2.25pt 1pt medium medium; padding: 0cm 5.4pt; background: red none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 16.5pt;" valign="top" width="64">

[FONT=&quot]E (3)<o

></o

>[/FONT]

</td> <td style="border-style: double solid none none; border-color: navy navy -moz-use-text-color -moz-use-text-color; border-width: 2.25pt 1pt medium medium; padding: 0cm 5.4pt; background: red none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 16.5pt;" valign="top" width="64">

[FONT=&quot]E (1)<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15.75pt;"> <td style="border: 1pt solid navy; padding: 0cm 5.4pt; width: 138pt; height: 15.75pt;" valign="top" width="184"> [FONT=&quot]B. [FONT=&quot]Likely<o

></o

>[/FONT][/FONT]
</td> <td style="border-style: solid solid solid none; border-color: navy navy navy -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; background: rgb(141, 179, 226) none repeat scroll 0% 0%; width: 115pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="153">

[FONT=&quot]M (19)<o

></o

>[/FONT]

</td> <td style="border-style: solid solid none none; border-color: navy navy -moz-use-text-color -moz-use-text-color; border-width: 1pt 1pt medium medium; padding: 0cm 5.4pt; background: yellow none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]H (14)<o

></o

>[/FONT]

</td> <td style="border-style: solid solid none none; border-color: navy navy -moz-use-text-color -moz-use-text-color; border-width: 1pt 1pt medium medium; padding: 0cm 5.4pt; background: yellow none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]H (9)<o

></o

>[/FONT]

</td> <td style="border-style: solid solid solid none; border-color: navy navy navy -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; background: red none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]E(5)<o

></o

>[/FONT]

</td> <td style="border-style: solid solid solid none; border-color: navy navy navy -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; background: red none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]E (2)<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15.75pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color navy navy; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 138pt; height: 15.75pt;" valign="top" width="184"> [FONT=&quot]C. [FONT=&quot]Moderate<o

></o

>[/FONT][/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color navy navy -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; background: rgb(146, 208, 80) none repeat scroll 0% 0%; width: 115pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="153">

[FONT=&quot]L (22)<o

></o

>[/FONT]

</td> <td style="border-style: solid solid solid none; border-color: navy navy navy -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; background: rgb(141, 179, 226) none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]M (18)<o

></o

>[/FONT]

</td> <td style="border-style: solid solid none none; border-color: navy navy -moz-use-text-color -moz-use-text-color; border-width: 1pt 1pt medium medium; padding: 0cm 5.4pt; background: yellow none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]H (13)<o

></o

>[/FONT]

</td> <td style="border-style: none solid none none; border-color: -moz-use-text-color navy -moz-use-text-color -moz-use-text-color; border-width: medium 1pt medium medium; padding: 0cm 5.4pt; background: red none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]E (8)<o

></o

>[/FONT]

</td> <td style="border-style: none solid none none; border-color: -moz-use-text-color navy -moz-use-text-color -moz-use-text-color; border-width: medium 1pt medium medium; padding: 0cm 5.4pt; background: red none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]E (4)<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15.75pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color navy navy; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 138pt; height: 15.75pt;" valign="top" width="184"> [FONT=&quot]D. [FONT=&quot]Unlikely<o

></o

>[/FONT][/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color navy navy -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; background: rgb(146, 208, 80) none repeat scroll 0% 0%; width: 115pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="153">

[FONT=&quot]L (24)<o

></o

>[/FONT]

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color navy navy -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; background: rgb(146, 208, 80) none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]L (21)<o

></o

>[/FONT]

</td> <td style="border-style: solid solid solid none; border-color: navy navy navy -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; background: rgb(141, 179, 226) none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]M (17)<o

></o

>[/FONT]

</td> <td style="border-style: solid solid solid none; border-color: navy navy navy -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; background: yellow none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]H (12) <o

></o

>[/FONT]

</td> <td style="border-style: solid solid none none; border-color: navy navy -moz-use-text-color -moz-use-text-color; border-width: 1pt 1pt medium medium; padding: 0cm 5.4pt; background: red none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]E (7)<o

></o

>[/FONT]

</td> </tr> <tr style="height: 15.75pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color navy navy; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 138pt; height: 15.75pt;" valign="top" width="184"> [FONT=&quot]E. [FONT=&quot]Rare<o

></o

>[/FONT][/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color navy navy -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; background: rgb(146, 208, 80) none repeat scroll 0% 0%; width: 115pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="153">

[FONT=&quot]L (25)<o

></o

>[/FONT]

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color navy navy -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; background: rgb(146, 208, 80) none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]L (23)<o

></o

>[/FONT]

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color navy navy -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; background: rgb(141, 179, 226) none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]M (20)<o

></o

>[/FONT]

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color navy navy -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; background: yellow none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]H (16)<o

></o

>[/FONT]

</td> <td style="border-style: solid solid solid none; border-color: navy windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0cm 5.4pt; background: yellow none repeat scroll 0% 0%; width: 48pt; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial; height: 15.75pt;" valign="top" width="64">

[FONT=&quot]H (11)<o

></o

>[/FONT]

</td> </tr> </tbody></table> [FONT=&quot]<o

> </o

>[/FONT]
[FONT=&quot]<o

> </o

>[/FONT]
[FONT=&quot]And I have a spreadsheet:<o

></o

>[/FONT]
[FONT=&quot]<o

> </o

>[/FONT]
<table class="MsoNormalTable" style="width: 203pt; border-collapse: collapse;" width="271" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 35.25pt;"> <td style="padding: 0cm 5.4pt; width: 65pt; height: 35.25pt;" valign="bottom" width="87" nowrap="nowrap"> [FONT=&quot]E<o></o>[/FONT]
</td> <td style="padding: 0cm 5.4pt; width: 61pt; height: 35.25pt;" valign="bottom" width="81" nowrap="nowrap"> [FONT=&quot]F<o></o>[/FONT]
</td> <td style="padding: 0cm 5.4pt; width: 37pt; height: 35.25pt;" valign="bottom" width="49" nowrap="nowrap"> [FONT=&quot]G<o></o>[/FONT]
</td> <td style="padding: 0cm 5.4pt; width: 40pt; height: 35.25pt;" valign="bottom" width="53" nowrap="nowrap"> [FONT=&quot]H<o></o>[/FONT]
</td> </tr> <tr style="height: 56.25pt;"> <td colspan="4" style="border: 1pt solid windowtext; padding: 0cm 5.4pt; width: 203pt; height: 56.25pt;" valign="top" width="271">

[FONT=&quot]Risk<o></o>[/FONT]

</td> </tr> <tr style="height: 66pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 65pt; height: 66pt;" valign="bottom" width="87">

[FONT=&quot]Likelihood <o></o>[/FONT]

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 61pt; height: 66pt;" valign="bottom" width="81">

[FONT=&quot]Consequence<o></o>[/FONT]

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 37pt; height: 66pt;" valign="bottom" width="49">

[FONT=&quot]Level (E,H,M,L)<o></o>[/FONT]

</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 40pt; height: 66pt;" valign="bottom" width="53">

[FONT=&quot]Rank (1 to 25)<o></o>[/FONT]

</td> </tr> <tr style="height: 17.25pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 65pt; height: 17.25pt;" valign="top" width="87"> [FONT=&quot]A. Almost Certain<o

></o

>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 61pt; height: 17.25pt;" valign="top" width="81"> [FONT=&quot]1. Negligible <o

></o

>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 37pt; height: 17.25pt;" valign="top" width="49"> [FONT=&quot] <o

></o

>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 40pt; height: 17.25pt;" valign="top" width="53"> [FONT=&quot] <o

></o

>[/FONT]
</td> </tr> <tr style="height: 23.25pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 65pt; height: 23.25pt;" valign="top" width="87"> [FONT=&quot] <o

></o

>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 61pt; height: 23.25pt;" valign="top" width="81"> [FONT=&quot] <o

></o

>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 37pt; height: 23.25pt;" valign="top" width="49"> [FONT=&quot] <o

></o

>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 40pt; height: 23.25pt;" valign="top" width="53"> [FONT=&quot] <o

></o

>[/FONT]
</td> </tr> <tr style="height: 23.25pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 65pt; height: 23.25pt;" valign="top" width="87"> [FONT=&quot] <o

></o

>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 61pt; height: 23.25pt;" valign="top" width="81"> [FONT=&quot] <o

></o

>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 37pt; height: 23.25pt;" valign="top" width="49"> [FONT=&quot] <o

></o

>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 40pt; height: 23.25pt;" valign="top" width="53"> [FONT=&quot] <o

></o

>[/FONT]
</td> </tr> <tr style="height: 16.5pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0cm 5.4pt; width: 65pt; height: 16.5pt;" valign="top" width="87"> [FONT=&quot] <o

></o

>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 61pt; height: 16.5pt;" valign="top" width="81"> [FONT=&quot] <o

></o

>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 37pt; height: 16.5pt;" valign="top" width="49"> [FONT=&quot] <o

></o

>[/FONT]
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0cm 5.4pt; width: 40pt; height: 16.5pt;" valign="top" width="53"> [FONT=&quot] <o

></o

>[/FONT]
</td> </tr> </tbody></table> [FONT=&quot]<o

> </o

>[/FONT]
[FONT=&quot]<o

> </o

>[/FONT]
[FONT=&quot]And I need column G to have a formula or function that says (for example):<o

></o

>[/FONT]
[FONT=&quot]<o

> </o

>[/FONT]
[FONT=&quot]IF Column E = “A. Almost Certain” and Column F = “Insignificant” THEN Colum G = H(15)<o

></o

>[/FONT]
[FONT=&quot]<o

> </o

>[/FONT]
[FONT=&quot]With all of the alternatives included.[/FONT]

Any help would be greatly appreciated.

Thanks!

[FONT=&quot]<o

></o

>[/FONT]

Peter_SSs · Sep 15, 2009

HayleyS

Welcome to the MrExcel board!

Not really clear to me. Your spreadsheet contains "Negligible" in the Consequence column but your Risk Assessment table does not contain "Negligible" as a consequence.

However, see if this might point you in the right direction.

Excel Workbook

E

F

G

H

I

J

K

L

M

N

O

1

Likelihood

Consequence

Level (E,H,M,L)

Rank (1 to 25)

Likelihood

Insignificant

Minor

Moderate

Major

Catastrophic

2

Almost Certain

Insignificant

H (15)

1

2

3

4

5

3

Almost Certain

H (15)

H (10)

E (6)

E (3)

E (1)

4

Likely

M (19)

H (14)

H (9)

E(5)

E (2)

5

Moderate

L (22)

M (18)

H (13)

E (8)

E (4)

6

Unlikely

L (24)

L (21)

M (17)

H (12)

E (7)

7

Rare

L (25)

L (23)

M (20)

H (16)

H (11)

8

Risks

HayleyS · Sep 15, 2009

Hi Peter,

Thank you for your help. It looks like it should work however when I try it it comes up with an "N/A" Error.

After looking into the Calculation Steps the message is:
Evaluation:

=INDEX($Z$22:$AD$26,MATCH("A. Almost Certain",$Y$22:$Y$26,0),MATCH(F4,$Z$20:$AD$20,0))
The next evaluation will result in an error.

Then:
Evaluation:
=INDEX($Z$22:$AD$26,#N/A,MATCH(F4,$Z$20:$AD$20,0))

Any further suggestions would be really appreciated.

Thanks again,
Hayley

Peter_SSs · Sep 29, 2009

Sorry for the slow response - I have not been around the board much in the last couple of weeks. That error message would indicate that "A. Almost Certain" is not found in the range $Y$22:$Y$26. Are you sure that it is present in that range? Even an extra (or missing) space would be significant.

Try copying my data (and ranges used) into a blank worksheet and testing the formula there. Try changing the values in E2 and F2 to see if the formula result in G2 adjusts as you would expect.

HayleyS · Sep 29, 2009

Hi Peter,

It was present in the range so am unsure what happened there.
I have since found that a VLOOKUP Formula using tables works well for this problem:

Eg. =VLOOKUP(E6,Risk_table,IF(F6='Sheet 2'!B20,2,IF(F6='Sheet 2'!C20,3,IF(F6='Sheet 2'!D20,4,IF(F6='Sheet 2'!E20,5,IF(F6='Sheet 2'!F20,6))))))

Thank you for all your help.

Hayley

Peter_SSs · Sep 30, 2009

HayleyS said:
Hi Peter,

It was present in the range so am unsure what happened there.
I have since found that a VLOOKUP Formula using tables works well for this problem:

Eg. =VLOOKUP(E6,Risk_table,IF(F6='Sheet 2'!B20,2,IF(F6='Sheet 2'!C20,3,IF(F6='Sheet 2'!D20,4,IF(F6='Sheet 2'!E20,5,IF(F6='Sheet 2'!F20,6))))))

Thank you for all your help.

Hayley

In that case, try:

=VLOOKUP(E6,Risk_table,MATCH(F6,'Sheet 2'!B20:F20,0)+1)

Formula/Function Required - Please help! :)

HayleyS

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

HayleyS

New Member

Peter_SSs

MrExcel MVP, Moderator

HayleyS

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

Formula/Function Required - Please help! :)

HayleyS

New Member

Excel Facts

Peter_SSs

MrExcel MVP, Moderator

HayleyS

New Member

Peter_SSs

MrExcel MVP, Moderator

HayleyS

New Member

Peter_SSs

MrExcel MVP, Moderator

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock