Modified Risk Map

[Seahorse]

This is a cross post from this the Risk Map page to allow the spreadsheet to be attached.

lysenkiy says:
March 27, 2014 at 8:29 pm
Mike,
Could you post your version of the matrix please?

This is great but there are a couple of changes I am struggling to incorporate. Firstly the grid needs to be 5 x 5 to support likelyhood values of:
Almost certain
Likely
Possible
Unlikeley
Rare
And Impacts of:
Insignificant
Minor
Moderate
Major
Severe

I have added these to the Setup tab and altered column F in Risk Register Tab to:
=IFERROR(CHOOSE(MATCH([@Probability],lstProbability,0),5,4,3,2,1),”")

It’s not clear to me if I need to alter the formula for column G and I have almost no knowledge of VBA to know it that needs adjusting at all.

The other aspect I would like to see changed is to make this project centric, not programme, to highlight different tasks with risks as opposed to whole projects, which would then see make this spreadsheet awesome. That said it's probably enought to change the Project column to the actual Risk.

 

[Faseeh]

Please see this file.. does that helps?

 

[Seahorse]

Yes, simplifies it considerably, many thanks.

One issue, if two projects have the same rating Say B2, only one appears in the Risk Matrix in B2.

 

[DianaB]

Yes, simplifies it considerably, many thanks.

One issue, if two projects have the same rating Say B2, only one appears in the Risk Matrix in B2.

In the sample spreadsheet, how can you get more than one entry with same ratings to show up?

Also, if you want to see a count of the entries instead of the name, is there a way for the marker to be larger with higher counts?

 

[Seahorse]

Yes, simplifies it considerably, many thanks.

One issue, if two projects have the same rating Say B2, only one appears in the Risk Matrix in B2.

I have played with this some more, but I don't seem to be able to insert more than one entry with the same rating in a cell. Any suggestions please?

 

[Hui]

Have a look at this version, attached:

 

[Seahorse]

That's an elegant an simpler approach to the problem that does the job. Thank you Sir.

 

[Seahorse]

That's an elegant an simpler approach to the problem that does the job. Thank you Sir.

On further testing, changing the status to closed or Ongoing, them populates column G with some odd answers? I have a ripping headache, and don't feel up to nesting IFs!

Correction closed does work, just ongoing, however I am going for a lie down before this headache kills me...

 

[Hui]

What combination of values is odd as it appears ok when I tested it
a Screen shot of the Risk Register page will do

 

[Seahorse]

Apologies, I was really quite ill yesterday and unable to think straight.

OK, if you change status to "On Going", Matrix coloumn is blanked and your ongoing risk dissapears from the Risk Matrix. I have added an OR to fix that.

=IF(OR(E8="Open",E8="On Going"),C8&"-"&D8,"")

 

[Hui]