MATRIX

[mahadeshwar]

Hi all,

really difficult one here.

I have a risk mairx i need to program into excel

i want to use conditonal formatting to specify the colour of the cell but it is difficult

for eg

on the risk matirx there are two things likelhood and impact

tp find out the risk score you multply the likelhood value by the impact value

maxium values both can have is 5 On the matrix the sum of these two values correspond to a colour on the matrix

but the problem is that two products can be the same colour

see attachment jpg

how do i program this into excel so when

i input the likelhood score in one cell and the impact score in another cell

it gives e the correct product and colours the cell with the correct colour that corresponds to the matrix

IS IT IMPOSSIBLE!!!!

[acw]

Hi

I don't believe that conditional formatting can be used here as you have at least 5 colors (4 risk plus the default). However, you could use a sheet event macro to perform the cell formatting. Have a look at the attached file and see if it can help.


Tony

[lucas]

Here's another approach. Rows 7-11 are hidden
not complete but a start...

[TonyJollans]

You should be able to do this with CF. Forget about products and sums as they don't correspond to the colours and just add unnecessary complication.

Assuming you have cells called Likelihood and Impact (you'll have to adjust for your actual cells) ...

Color your cell green to start with and add CF:

Condition 1 - colour Yellow, Formulas Is: =OR(AND(Likelihood>=4,Impact=1),AND(Likelihood>=3,Impact=2),AND(Likelihood<=2,Impact=3))

Condition 2 - colour Orange, Formulas Is: =OR(AND(Likelihood>=3,Impact=3),AND(Likelihood<=2,Impact=4))

Condition 3 - colour Red: Formulas Is: =OR(AND(Likelihood>=3,Impact=4),Impact=5)

[lucas]

I thought about that Tony but he has more than 3 conditions and....they overlap? The overlapping conditon is the problem.

My (brute force method-post 3)file works on only 2 cells for input.....and 1 for result and I am sure they want it to work on 3 columns, 2 for the input and then the result behind in each row.

any ideas....

[TonyJollans]

Hi Steve,

He has four desired result formats - the number of conditions is more or less irrelevant - so a default and 3 CFs should do the trick.

After posting I thought about it and decided a default of yellow would be better than green. I'll knock up a sample workbook and post it shortly.

[lucas]

watch out for the overlapping values issue......thats where I ran into problems...this is a pretty interesting problem.

[mahadeshwar]

thanks guys for all your help.

Its difficult as i am an exxel novice

all the code you have all given me is great but due to my lack of knowledge i cant edit it to do what i want.


you know

D cells - value
E cell = value
F cell the value and colour

i can only get it to one for one set of cells not many

THANKS

[TonyJollans]

Here's an example - you do get yellow cells where I have set up the CF and not entered any data but that could easily be sorted on a finished worksheet - and the restriction to 3 formats goes away in 2007 so it's a short term issue :)

I was just about to upload when I saw mahadeshwar's latest post so I went back and changed the columns - hope I didn't mess it up in the process.

[lucas]

That's brilliant Tony...great use of nested conditions. I tested it under all conditions and it works perfectly....thanks.

[lucas]

for mahadeshwar,
I added the formula to sum the risk factors to Tony's file. See attachment.

[mahadeshwar]

Thank you tony and lucas you have both helped my out so much really sorry for late reply have been reALLY ILL thank you both loads

i got one word to sum you both up "genius"

[lucas]

Tony is the one to thank....hope your feeling better.