PDA

View Full Version : MATRIX

05-11-2006, 11:34 AM
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
05-11-2006, 05:14 PM
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
05-11-2006, 07:02 PM
Here's another approach. Rows 7-11 are hidden
not complete but a start...

TonyJollans
05-12-2006, 05:32 AM
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) ...

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
05-12-2006, 06:28 AM
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
05-12-2006, 08:26 AM
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
05-12-2006, 08:30 AM
watch out for the overlapping values issue......thats where I ran into problems...this is a pretty interesting problem.

05-12-2006, 08:38 AM
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
05-12-2006, 08:49 AM
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
05-12-2006, 09:19 AM
That's brilliant Tony...great use of nested conditions. I tested it under all conditions and it works perfectly....thanks.

lucas
05-12-2006, 09:32 AM