Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Risk & Issue Register and risk maps
To all interested and involved in this area. I am looking for any
ideas on creating a risk map in excel, based on risk values scored in a risk register. Presentation-wise, I would like to develop a 10 x 10 matrix, with the two axis named consequence/impact and likelihood. Where a risk is scored within a particular value in the Risk register worksheet, (or a summary of risks in a worksheet), I would like a link to that risk to be posted into the appropriate cell of the risk map. For example, if a risk scores 5 on impact x 9 on likeliood, (score 45), in the risk register worksheet I would like a link to that risk to appear in the appropriate cell of the risk map, i.e. in the cell intersect of 5 and 9 of the risk map. The reason I would like for a link to be posted is to allow the user to navigate to the risk and view the detail, by jsut clicking on the link within the appropriate risk map cell. Ideally, I would like the name of the link to be ranked in accordance to the other risks in the risk register. For example, if the risk that scores 45 is ranked 30th out of 80 risks that have been assessed, then in the risk map I would like the link to be named 30, and when selected to link the user to the risk register worksheet where further details can be viewed. All of the above to update as risks are added to the risk register worksheet(s). I appreciate that this complex, but would like to know if it is feasible. Regards, NiallD |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Risk & Issue Register and risk maps
One venture which delivers it ..
Illustrated in this sample construct at: http://www.savefile.com/files/768019 Plotting risk map w hyperlink to risk register.xls In a sheet: X (ie the Risk Register Reference) Col A= Risk#s, Col B = Impact#, Col C = Likelihood#, with data from row2 to say, row10 In D1:F1 are the labels: Score, Rank, Coord Put in D2: =IF(OR(B2="",C2=""),"",B2*C2) Put in E2: =IF(ISERROR(RANK(D2,$D$2:$D$10)),"",RANK(D2,$D$2:$ D$10)) Put in F2: =IF(OR(B2="",C2=""),"",VLOOKUP(B2,{1,"B";2,"C";3," D";4,"E";5,"F";6,"G";7,"H";8,"I";9,"J";10,"K"},2,0 )&C2+1) Select D2:F2, copy down to D10 Then in a sheet: Risk Map, Impact#s: 1,2, ... 10 are labelled in B1:K1, while Likelihood#s: 1,2, .. 10 are labelled in A2:A11 Put in B2: =IF(ISNA(MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0 )),"",HYPERLINK("#"&CELL("address",INDIRECT("'X'!A "&MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0))),IND EX(X!$E:$E,MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F ,0)))) Copy B2 across by 10 cols to K2, fill down to K11 to populate the 10x10 matrix. B2:K11 will yield the required results, ie it'll "plot" the rank numbers from X's col E in the correct cells as hyperlinks. And clicking on the hyperlinks in the risk map will jump directly to the appropriate line in X (ie the Risk Register Reference) for more details, as desired. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message oups.com... To all interested and involved in this area. I am looking for any ideas on creating a risk map in excel, based on risk values scored in a risk register. Presentation-wise, I would like to develop a 10 x 10 matrix, with the two axis named consequence/impact and likelihood. Where a risk is scored within a particular value in the Risk register worksheet, (or a summary of risks in a worksheet), I would like a link to that risk to be posted into the appropriate cell of the risk map. For example, if a risk scores 5 on impact x 9 on likeliood, (score 45), in the risk register worksheet I would like a link to that risk to appear in the appropriate cell of the risk map, i.e. in the cell intersect of 5 and 9 of the risk map. The reason I would like for a link to be posted is to allow the user to navigate to the risk and view the detail, by jsut clicking on the link within the appropriate risk map cell. Ideally, I would like the name of the link to be ranked in accordance to the other risks in the risk register. For example, if the risk that scores 45 is ranked 30th out of 80 risks that have been assessed, then in the risk map I would like the link to be named 30, and when selected to link the user to the risk register worksheet where further details can be viewed. All of the above to update as risks are added to the risk register worksheet(s). I appreciate that this complex, but would like to know if it is feasible. Regards, NiallD |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Risk & Issue Register and risk maps
On May 31, 11:13 am, "Max" wrote:
One venture which delivers it .. Illustrated in this sample construct at:http://www.savefile.com/files/768019 Plotting risk map w hyperlink to risk register.xls In a sheet: X (ie the Risk Register Reference) Col A= Risk#s, Col B = Impact#, Col C = Likelihood#, with data from row2 to say, row10 In D1:F1 are the labels: Score, Rank, Coord Put in D2: =IF(OR(B2="",C2=""),"",B2*C2) Put in E2: =IF(ISERROR(RANK(D2,$D$2:$D$10)),"",RANK(D2,$D$2:$ D$10)) Put in F2: =IF(OR(B2="",C2=""),"",VLOOKUP(B2,{1,"B";2,"C";3," D";4,"E";5,"F";6,"G";7,"H*";8,"I";9,"J";10,"K"},2, 0)&C2+1) Select D2:F2, copy down to D10 Then in a sheet: Risk Map, Impact#s: 1,2, ... 10 are labelled in B1:K1, while Likelihood#s: 1,2, .. 10 are labelled in A2:A11 Put in B2: =IF(ISNA(MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0 )),"",HYPERLINK("#"&CELL(*"address",INDIRECT("'X'! A"&MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0))),IN D*EX(X!$E:$E,MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F: $F,0)))) Copy B2 across by 10 cols to K2, fill down to K11 to populate the 10x10 matrix. B2:K11 will yield the required results, ie it'll "plot" the rank numbers from X's col E in the correct cells as hyperlinks. And clicking on the hyperlinks in the risk map will jump directly to the appropriate line in X (ie the Risk Register Reference) for more details, as desired. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik wrote in message oups.com... To all interested and involved in this area. I am looking for any ideas on creating a risk map in excel, based on risk values scored in a risk register. Presentation-wise, I would like to develop a 10 x 10 matrix, with the two axis named consequence/impact and likelihood. Where a risk is scored within a particular value in the Risk register worksheet, (or a summary of risks in a worksheet), I would like a link to that risk to be posted into the appropriate cell of the risk map. For example, if a risk scores 5 on impact x 9 on likeliood, (score 45), in the risk register worksheet I would like a link to that risk to appear in the appropriate cell of the risk map, i.e. in the cell intersect of 5 and 9 of the risk map. The reason I would like for a link to be posted is to allow the user to navigate to the risk and view the detail, by jsut clicking on the link within the appropriate risk map cell. Ideally, I would like the name of the link to be ranked in accordance to the other risks in the risk register. For example, if the risk that scores 45 is ranked 30th out of 80 risks that have been assessed, then in the risk map I would like the link to be named 30, and when selected to link the user to the risk register worksheet where further details can be viewed. All of the above to update as risks are added to the risk register worksheet(s). I appreciate that this complex, but would like to know if it is feasible. Regards, NiallD- Hide quoted text - - Show quoted text - Max, Seldom do you get a response like yours that fully understands your problem and gives you a workable solution. Many thanks, Niall |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Risk & Issue Register and risk maps
On May 31, 11:13 am, "Max" wrote:
One venture which delivers it .. Illustrated in this sample construct at:http://www.savefile.com/files/768019 Plotting risk map w hyperlink to risk register.xls In a sheet: X (ie the Risk Register Reference) Col A= Risk#s, Col B = Impact#, Col C = Likelihood#, with data from row2 to say, row10 In D1:F1 are the labels: Score, Rank, Coord Put in D2: =IF(OR(B2="",C2=""),"",B2*C2) Put in E2: =IF(ISERROR(RANK(D2,$D$2:$D$10)),"",RANK(D2,$D$2:$ D$10)) Put in F2: =IF(OR(B2="",C2=""),"",VLOOKUP(B2,{1,"B";2,"C";3," D";4,"E";5,"F";6,"G";7,"H*";8,"I";9,"J";10,"K"},2, 0)&C2+1) Select D2:F2, copy down to D10 Then in a sheet: Risk Map, Impact#s: 1,2, ... 10 are labelled in B1:K1, while Likelihood#s: 1,2, .. 10 are labelled in A2:A11 Put in B2: =IF(ISNA(MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0 )),"",HYPERLINK("#"&CELL(*"address",INDIRECT("'X'! A"&MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F:$F,0))),IN D*EX(X!$E:$E,MATCH(ADDRESS(ROW(),COLUMN(),4),X!$F: $F,0)))) Copy B2 across by 10 cols to K2, fill down to K11 to populate the 10x10 matrix. B2:K11 will yield the required results, ie it'll "plot" the rank numbers from X's col E in the correct cells as hyperlinks. And clicking on the hyperlinks in the risk map will jump directly to the appropriate line in X (ie the Risk Register Reference) for more details, as desired. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik wrote in message oups.com... To all interested and involved in this area. I am looking for any ideas on creating a risk map in excel, based on risk values scored in a risk register. Presentation-wise, I would like to develop a 10 x 10 matrix, with the two axis named consequence/impact and likelihood. Where a risk is scored within a particular value in the Risk register worksheet, (or a summary of risks in a worksheet), I would like a link to that risk to be posted into the appropriate cell of the risk map. For example, if a risk scores 5 on impact x 9 on likeliood, (score 45), in the risk register worksheet I would like a link to that risk to appear in the appropriate cell of the risk map, i.e. in the cell intersect of 5 and 9 of the risk map. The reason I would like for a link to be posted is to allow the user to navigate to the risk and view the detail, by jsut clicking on the link within the appropriate risk map cell. Ideally, I would like the name of the link to be ranked in accordance to the other risks in the risk register. For example, if the risk that scores 45 is ranked 30th out of 80 risks that have been assessed, then in the risk map I would like the link to be named 30, and when selected to link the user to the risk register worksheet where further details can be viewed. All of the above to update as risks are added to the risk register worksheet(s). I appreciate that this complex, but would like to know if it is feasible. Regards, NiallD- Hide quoted text - - Show quoted text - Max, One further query. Using the above structure, what if I have two risks that are scored equally, i.e. they have a joint rank. Can the risk map show two, (or more), hyperlinks back to the Risk Register? Regards, Niall |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Risk & Issue Register and risk maps
wrote in message
ups.com.. Max, Seldom do you get a response like yours that fully understands your problem and gives you a workable solution. Many thanks Welcome, glad you liked it .. One further query. Using the above structure, what if I have two risks that are scored equally, i.e. they have a joint rank. Can the risk map show two, (or more), hyperlinks back to the Risk Register? I'm not sure that's possible. One way around it would be to use a tiebreaker col in the risk register and then point the ranking to it. In the sample file, in X, Put in G2: =IF(D2="","",D2-ROW()/10^10) Copy down to G10. This is the tiebreaker col. Then replace the rank formula in E2 with: =IF(ISERROR(RANK(G2,$G$2:$G$10)),"",RANK(G2,$G$2:$ G$10)) Copy down to E10 The above will ensure that all risks in X would be fully reflected in the risk map, tied scores notwithstanding. In the event of tied scores, the risk that is "higher up" in X would be ranked above the other risk located "lower down" in X. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Risk & Issue Register and risk maps
On May 31, 1:38 pm, "Max" wrote:
wrote in message ups.com.. Max, Seldom do you get a response like yours that fully understands your problem and gives you a workable solution. Many thanks Welcome, glad you liked it .. One further query. Using the above structure, what if I have two risks that are scored equally, i.e. they have a joint rank. Can the risk map show two, (or more), hyperlinks back to the Risk Register? I'm not sure that's possible. One way around it would be to use a tiebreaker col in the risk register and then point the ranking to it. In the sample file, in X, Put in G2: =IF(D2="","",D2-ROW()/10^10) Copy down to G10. This is the tiebreaker col. Then replace the rank formula in E2 with: =IF(ISERROR(RANK(G2,$G$2:$G$10)),"",RANK(G2,$G$2:$ G$10)) Copy down to E10 The above will ensure that all risks in X would be fully reflected in the risk map, tied scores notwithstanding. In the event of tied scores, the risk that is "higher up" in X would be ranked above the other risk located "lower down" in X. -- Max Singaporehttp://savefile.com/projects/236895 xdemechanik --- Max, This makes sense. I can let management decide which risk the wish to prioritise as a joint risk by using their noodle. One last query, if I may - the convention that the management team are used to for presenting risk maps go in the reverse order to what you have done. How can I have the highest possible risks, (i.e. Likelihood=10; Consequence=10), in the top right hand corner of the risk map? This would have the "Y" axis, (Likelihood) in reverse direction starting at "1" in Cell A11. The coordinates resolved in the X worksheet would have to adjust accordingly. Best regards, Niall |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Risk & Issue Register and risk maps
How can I have the highest possible risks, (i.e. Likelihood=10;
Consequence=10), in the top right hand corner of the risk map? In the sample's sheet: X, Just replace the formula in F2 (Coord col) with: =IF(OR(B2="",C2=""),"",VLOOKUP(B2,{10,"B";9,"C";8, "D";7,"E";6,"F";5,"G";4,"H";3,"I";2,"J";1,"K"},2,0 )&C2+1) Copy down to F10. That should do it. Re-label accordingly the "y-axis" (Likelihood) in Risk Map -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- wrote in message oups.com... This makes sense. I can let management decide which risk the wish to prioritise as a joint risk by using their noodle. One last query, if I may - the convention that the management team are used to for presenting risk maps go in the reverse order to what you have done. How can I have the highest possible risks, (i.e. Likelihood=10; Consequence=10), in the top right hand corner of the risk map? This would have the "Y" axis, (Likelihood) in reverse direction starting at "1" in Cell A11. The coordinates resolved in the X worksheet would have to adjust accordingly. Best regards, Niall |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Risk & Issue Register and risk maps
Apologies, pl dismiss the earlier response for now.
It wasn't correct. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Risk & Issue Register and risk maps
Here's the corrected version ..
How can I have the highest possible risks, (i.e. Likelihood=10; Consequence=10), in the top right hand corner of the risk map? In the sample's sheet: X, Replace the formula in F2 (Coord col) with: =IF(OR(B2="",C2=""),"",VLOOKUP(B2,{1,"B";2,"C";3," D";4,"E";5,"F";6,"G";7,"H";8,"I";9,"J";10,"K"},2,0 )&12-C2) Copy down to F10. That should do it. Re-label accordingly the "y-axis" (Likelihood) in Risk Map -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Risk & Issue Register and risk maps
Here's a link to the revised sample:
http://www.savefile.com/files/770124 Plotting risk map w hyperlink to risk register_v2.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to set out a fire risk documunt | Excel Discussion (Misc queries) | |||
Need excel tool that helps assess, score, rank Supplier Risk | Excel Discussion (Misc queries) | |||
How do I select colors instead of numbers for rating risk? | Excel Worksheet Functions | |||
Risk Issues register | Excel Discussion (Misc queries) | |||
Risk management | Excel Discussion (Misc queries) |