Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
Posts: 9,221
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to set out a fire risk documunt kate Excel Discussion (Misc queries) 1 November 22nd 06 03:11 PM
Need excel tool that helps assess, score, rank Supplier Risk terry Excel Discussion (Misc queries) 0 October 19th 06 08:56 PM
How do I select colors instead of numbers for rating risk? Patrick Excel Worksheet Functions 2 September 7th 06 03:55 PM
Risk Issues register sacrum Excel Discussion (Misc queries) 4 January 19th 06 01:25 PM
Risk management Ole w Excel Discussion (Misc queries) 0 July 28th 05 01:35 PM


All times are GMT +1. The time now is 06:45 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"