RAG rating with percentages

[Shp1]

Hi Guys,

Just keep stumbling on this one - I'm trying to do a RAG rating with multiple cells and rules.

There are percentages in columns B,C,D,E.

Now the rule that I would like to set is that if the value in column B is < 50% then the number I should see in column F should be 2 and if over 50% then the name in column F should change to 3.

Similarly, if there is a value >50% in column E then the number in column F should change to 1.

The twist is that if there is a percentage above 0 in column C and/or D then the number in column F should automatically change to 3 irrespective of what the other results are for column B or E.

I then plan to use conditional formatting to colour code the results of 1,2 and 3 in column F to complete my RAG rating.

Can anyone help with this?

Example:

 

[etaf]

is there an order for all these 4 columns
you say

The twist is that if there is a percentage above 0 in column C and/or D then the number in column F should automatically change to 3 irrespective of what the other results are for column B or E.

So C or D - come first
=IF( OR(C2>0,D2>0,B2>0.5) , 3 ,

which is more important B or E

=IF( OR(C2>0,D2>0,B2>0.5) , 3 , IF(b2<0.5, 2, if(E2>0.5, 1,0)))

you slso say , <50 or >50 - what if the result is exactly 50% - for that condition add an =

Book8
	A	B	C	D	E	F
1		minor	major	critical	RFT	RAG
2		100.00%	0.00%	0.00%	0.00%	3
3		78.95%	10.53%	0.00%	10.53%	3
4		75.00%	25.00%	0.00%	0.00%	3
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=IF( OR(C2>0,D2>0,B2>0.5), 3, IF(B2<0.5, 2, IF(E2>0.5, 1,0)))

 

[Shp1]

=IF( OR(C2>0,D2>0,B2>0.5), 3, IF(B2<0.5, 2, IF(E2>0.5, 1,0)))

is there an order for all these 4 columns
you say

So C or D - come first
=IF( OR(C2>0,D2>0,B2>0.5) , 3 ,

which is more important B or E

=IF( OR(C2>0,D2>0,B2>0.5) , 3 , IF(b2<0.5, 2, if(E2>0.5, 1,0)))

you slso say , <50 or >50 - what if the result is exactly 50% - for that condition add an =

Book8
	A	B	C	D	E	F
1		minor	major	critical	RFT	RAG
2		100.00%	0.00%	0.00%	0.00%	3
3		78.95%	10.53%	0.00%	10.53%	3
4		75.00%	25.00%	0.00%	0.00%	3
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=IF( OR(C2>0,D2>0,B2>0.5), 3, IF(B2<0.5, 2, IF(E2>0.5, 1,0)))

is there an order for all these 4 columns
you say

So C or D - come first
=IF( OR(C2>0,D2>0,B2>0.5) , 3 ,

which is more important B or E

=IF( OR(C2>0,D2>0,B2>0.5) , 3 , IF(b2<0.5, 2, if(E2>0.5, 1,0)))

you slso say , <50 or >50 - what if the result is exactly 50% - for that condition add an =

Book8
	A	B	C	D	E	F
1		minor	major	critical	RFT	RAG
2		100.00%	0.00%	0.00%	0.00%	3
3		78.95%	10.53%	0.00%	10.53%	3
4		75.00%	25.00%	0.00%	0.00%	3
Sheet1

Cell Formulas
Range	Formula
F2:F4	F2	=IF( OR(C2>0,D2>0,B2>0.5), 3, IF(B2<0.5, 2, IF(E2>0.5, 1,0)))

Thanks for the formula Etaf - Only one issue I found, the cell where E = 50% doesn't seem to show the number 1 in the result for the RAG rating. Any idea why this may be?

 

[etaf]

because B2 is less than 50%
IF(B2<0.5, 2,

the IF will work as a priority left to right

hence question in post

which is more important B or E

 

[Shp1]

because B2 is less than 50%
IF(B2<0.5, 2,

the IF will work as a priority left to right

hence question in post

Apologies, E is more important than B - If E is 50%+ then the result should be 1, if less than 50% then as long as there are no percentages in C or D the result should be 2.

 

[etaf]

=IF( OR(C2>0,D2>0,B2>0.5), 3, IF(B2<0.5, 2, IF(E2>0.5, 1,0)))
then we could change the IF - so the E2 is checked before B
=IF( OR(C2>0,D2>0,B2>0.5), 3, IF(E2>0.5, 1 , IF(B2<0.5, 2,0)))

The first is checking the 3 columns B , C , D - so if C or d is zero - then it will give 3 and stop OR if B2 >50 , then 3 and stop
so now E will be checked before B and if E2 is greater than or equal to 50% it will check B2

 

[Shp1]

Thank you, this worked out perfectly - apologies for the late reply

 

[etaf]

you are welcome