Risk Mapping / Analysis

[Gingerjohn]

I've got a risk matrix / log that rates risks by impact against probability: it then plots them on a 5 x 5 grid. Each risk has its own unique reference, to get Excel to plot the unique refence number against the point on the chart I've loaded in J-Walk's chart tools which works fine.

Problem is that when presenting to a Project Board it is usually only necessary to show risks that are open. When I filter the my list to show "Open" risks the problem occurs.

The risks remain plotted in the correct place in the Risk Map but the unique reference numbers get a bit screwed up.

Testing John's Risk template.xls
	B	C	D	E	F	G
11	Ref.	Probability(P)	Impact(I)		RiskRating
12						Status
13	T01	1	2	L	Low	Open
14	T02	4	4	VH	VeryHigh	Closed
15	T03	3	3	H	High	Closed
16	T04	2	5	H	High	Open
17	R01	1	4	H	High	Closed
18	R02	2	2	L	Low	Open
19	R03	2	3	M	Medium	Open
20	R04	3	1	L	Low	Open
21	P01	4	2	M	Medium	Closed
22	P02	5	5	VH	VeryHigh	Closed
23	P03	4	1	L	Low	Open
24	P04	2	3	M	Medium	Closed
25	X01	2	5	H	High	Open
26	X99	1	1	L	Low	Closed
27	X98	1	5	H	High	Open
28	X97	5	4	VH	VeryHigh	Closed
29	S55	3	2	M	Medium	Open
30	S63	3	2	M	Medium	Closed
31	S44	2	4	H	High	Open
Risk Log

The first one is correct which in the example is T01, however the next "Open" risk is T04, but on my chart this is plotted as T02, even though it is in T04's position - there is no problem with where the points are plotted it is just their reference numbers. So the next "Open" risk R02 appears in its correct position but is labelled T03 etc. etc. S44 appears as T02

I've tried Tools > Options > Chart > Plot visible cells checking and un-checking the box and also putting an apostrophe in front of each entry in the Reference column but to no avail.

[/i] I can't include the chart as we can't access image sharing sites at work so I can't give a URL for you to go view it at. Also I don't have Excel at home so I can't take the file home and post it from there. Just try and imagine a 5 x 5 grid that plots points in each box according to their scores (they don't overlap as I've use RANDBETWEEN to subtract between 0.25 and 0.75 off each whole number so the points sit in the middle of each box and not on the intersection).

Any help would be greatly appreciated particularly in relation to risk analysis.

Thanks.

 

[eliW]

Just an idea (not tested)
Enter another column near REF. put in formula :

=IF(G13="open",B13,NA())

and copy down. use this column instead REF.

 

[Gingerjohn]

Risk mapping / analysis

Tried it but it just plots about 4 x "#N/A" on the chart instead (along with some of the other reference numbers)

I'm wondering if it has anything to do with a custom list or something like that?

Excel seems to be taking the order that was originally in those cells and applying it to the ones that remain: it's not picking up the reference numbers that are remaining.

I showed one of my colleauges by changing the reference numbers to 1 - 20. Even though 1, 4, 6, 7, 8, 11, 13, 15, 17, 19 were left open they appeared on the chart as 1 - 10. They couldn't work it out either.

I've tried using absolute cell refs in the J-Walk chart tool but that hasn't worked either.