matrix/array/if formula

[dz6kb4]

Hi all,

Please can you help me with this problem I'm facing. I am creating a risk register and to assess a risk I am using a matrix (see file attached). I am not skilled enough with excel and arrays or whatever I need to do this so i am asking here. I basically need the value in the 'assessment' column to be populated in my register depedning on which values are entered in the 'probability' 'cost impact' and 'time impact' columns on the register. e.g. if the prob is 95, cost is 900,000 and time impact is 50 - VHI will be entered in the the assessment column.

Please shout if this doesnt make sense. i really hope you can help me with this.

many thanks.

Anthony.

[NBVC]

If you are going to post an attachment, please post an excel file that people can work with....

Thank you.

[dz6kb4]

oops, sorry. I've attached an excel sheet instead. I'm basically looking to populate the assessment column based on the guidance tab.

many thanks.

[NBVC]

Will there ever be some sort of mix...

Say, for example a Probability of 75% with Cost Impact in different category, like £150k?

Is so, what then would be the result?

[dz6kb4]

Hi thanks for looking at this.

Good question. To be honest, I hate this guidance matrix but I have to work with it to a certain degree. However, what if we change the matrix to calculate a factored value first?

i.e. 90%*500k = 450k. This means that if the prob*cost > 450k and >30 days, assessment = VHI
60%*250k = 150k.
90%8500k = 450k. if prob * cost is between 150k and 450k, and 25 days, assessment = HI

if there is 450k but 45 days, assessment should be the lower grade

thanks again

[NBVC]

I am not sure if the same question might apply about mix of that combination and the days???

Anyways, see attached... I created a new table (note: You only need lower limits and better to have in ascending order).

Is this a start... I have not incorporated Impact days.. as it would be same question as previously.. what if mix?

[dz6kb4]

hey. thanks alot for this. it's a great start. I do need to find away of incorporating the impact days in to this as it affects the severity of the risk. I get your point on the mix though. I need to find a way of adding the number of days to create a score and then assign an assessment based on a single score..hmmm

Many thanks for your help

[NBVC]

See this new attachment for an idea:

I created a table of values, but I filled in the same values in all the cells.. but you can change around so that the intersection of row headers (Cost Impact), column header(Time Impact) get you the result you want.

I changed up the formula in the other sheet to work with this table. It will pull the intersecting cell's contentents where your parameters line up.

Hope it helps.

[dz6kb4]

thanks so much for this. Just what I was trying to think of! I've adjusted the table to be more of a stepped pattern of highs medium lows etc but other than that..I think it's exactly what i needed.

Many thanks.

[NBVC]

Great! Glad that was a useful idea!

Good Luck.