Please see file this is what i am stuck on

[mahadeshwar]

Hi all, i have worked on this all day and i cant get it to work.

My cells in coloumn F have conditional formatting added to them.

If you see sheet 3 i have a graph that corresponds to the cells in coloumn f

But its not correct because if you see sheet 4 you can see my matirx 12 can be orange and 12 can be red. My graph currently works on numbers and cant because the same value can be a different colour

I have tried functions to and all but nothing works

All i can think of getting the CELL G to print the word HIGH MEDIUM OR LOW DEPENDING ON THE VALUE OF CELLS D AND E so that i can use it to print a correct graph

=OR(AND(D2<=3,E2=1),AND(D2<=2,E2=2) = the word green

but i cant do it any other ideas

[Bob Phillips]

Is this what you mean in C4:C7

=SUMPRODUCT(((Sheet1!D2200>=3)*(Sheet1!E2:E200=4))+((Sheet1!E2:E200=5)))
=SUMPRODUCT(((Sheet1!D2200>=3)*(Sheet1!E2:E200=3))+((Sheet1!D2200<=2)*(Sheet1!E2:E200=4)))
=COUNTA(Sheet1!B:B)-D4-D5-D7
=SUMPRODUCT(--(Sheet1!D2200>=3),--(ISNUMBER(MATCH(Sheet1!E2:E200,{4,5},0))))

[mahadeshwar]

Hi all,
thank you tony for your help below if this statement tony wrote

=IF(OR(AND(D2<=3,E2=1),AND(D2<=2,E2=2)),"HIGH","")

Tony said to nest the other conditions in the if statement to get the below, how do i do that
=OR (AND(D2>=3,E2=3),AND(D2<=2,E2=4)) = MEDIUM

=OR(AND(D2>=3,E2=4),E2=5) = RED
THANK YOU ALL

[TonyJollans]

There may be a better way but this sets text the same as the colour in the CF example - you can change the words to whatever you want:

=CHOOSE(E3,IF(D3<4,"Green","Yellow"),
           IF(D3<3,"Green","Yellow"),
           IF(D3<3,"Yellow","Orange"),
           IF(D3<3,"Orange","Red"),
                   "Red")

[mahadeshwar]

hi tony,

i dont understand the code what should i do.
our other code you gave me worked like a dream.

but i cant nest the rest of the statements because i am not great at excel

[TonyJollans]

If you have the Impact in cell E3 and the Likelihood in cell D3 (as per the workbook posted in the other thread) then put the posted formula in cell F3 - and copy down as far as you need.

This formula is complete - it doesn't need anything else adding to it.

[mahadeshwar]

TONY THANK YOU SO MUCH YOU ARE THE BEST YOU HAVE HELPED ME SO MUCH I WISH I COUD POST YOU A BOX OF ROSES THANK YOU

[TonyJollans]

I've taken a copy of my earlier posted workbook and put the formula in it - I've actually changed it slightly to work with blank Impacts. All you need to do is change the words in quotes - "Red" to "High", etc - I don't know what you want to call all four categories.

[TonyJollans]

After pressing Submit I see you don't need the sample. Glad you got it doing what you want.