PDA

View Full Version : Please see file this is what i am stuck on

05-18-2006, 08:16 AM
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
05-18-2006, 09:26 AM
Is this what you mean in C4:C7

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

05-18-2006, 01:50 PM
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
05-18-2006, 02:51 PM
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")

05-18-2006, 02:56 PM
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
05-18-2006, 03:45 PM
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.