Conditional Formatting based on key

[Mustafa Evans]

I'm having some problems with using Conditional Formatting in Excel. I have a data set with percentages in monthly columns. Each row is a reporting statement. I need to compare the current month to the previous one and if the change is 10% or more then the current month's cell should change colour.

I have a key in column AJ that states whether the change is good or bad. These include...

UP GOOD
DOWN GOOD
UP BAD
DOWN BAD

I'm always looking for a Significant change of 10% or more.

A good change should be shown in GREEN and a bad change in RED.

If either condition is not met then the cell should remain white.

Can anyone help please.

Mustafa

 

[rondeondo]

Whose formula?

Which I16? There have been several shown throughout the thread.

sorry was replying when you were so my comment is out of order, was referring to the conditional formatting formula.
Definitely need the criteria to be better explained and enough samples to illustrate the desired results. I'll leave you with this one, I think you have it well in hand

 

[Peter_SSs]

.. was replying when you were ..

Hmm, your post was more than 5 hours after mine.

Anyway, the OP seems happy with the suggestion.

 

[rondeondo]

maybe I hadn't looked at the second page of answers. all good.

 

[Mustafa Evans]

My

Perhaps you can see better now why I kept asking for examples before as we didn't have examples like these until now.

I'm hoping your comment in row 10 is wrong. Rows 10 & 11 both say "Down Bad" and both rows go up by 10% yet you say one should be red and one should be white? My current understanding says both should be white.

Try these

20 03 02.xlsm
	F	G	H	I	J	K																									AJ
1	Nov	Dec	Jan	Feb	Mar	Apr
2																															x
3				51%	0.61																										x
4				61%	71%																										Up Good
5				20%	10%																										Up Good
6				10%	20%																										Up Bad
7				40%	30%																										Up Bad
8				10%	20%																										Down Good
9				30%	20%																										Down Good
10				0%	10%																										Down Bad
11				10%	20%																										Down Bad
12				0%	10%																										Down Good
13																															x
14			98%	88%																											Up Good
15				98%																											x
16				17%	27%																										Up Bad
17				55%	45%																										Down Bad
18				79%	89%																										Down Good
19				28%	28%																										x
20				90%	100%																										Down Bad
CF Red Green (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:Q20	Expression	=AND(RIGHT($AJ2,3)="Bad",(G2-F2)*IF(LEFT($AJ2,2)="Up",1,-1)>=10%,COUNTA(G2:$Q2)=1)	text	NO
G2:Q20	Expression	=AND(RIGHT($AJ2,4)="Good",(G2-F2)*IF(LEFT($AJ2,2)="Up",1,-1)>=10%,COUNTA(G2:$Q2)=1)	text	NO

Further to the solution you gave, I now am told I need to improve it !!

The criteria in the key :

Up Good
Up Bad
Down Good
Down Bad

are subjective, as if the 1st criteria is met of being =>10%, and the key reads for example "Up Good", if in fact the result went down by 10% then the opposite is true and the cell should turn red.

Please remember that each key relates uniquely to each reporting statement, but if the key reads one way, then the opposite is also true and the cell should turn the opposite colour.

I hope my ramblings make sense.

 

[Peter_SSs]

I hope my ramblings make sense.

I'm not sure. You should be understanding by now that a good variety of examples helps clarify. Are these the correct colours for this data. For any that are incorrect please give a detailed explanation of what colour they should be and why.

Mustafa Evans 2020-03-06 1.xlsm
	H	I	J	K																									AJ
1	Jan	Feb	Mar	Apr
2																													x
3		51%	0.61																										x
4		61%	71%																										Up Good
5		20%	10%																										Up Good
6		10%	20%																										Up Bad
7		40%	38%																										Up Bad
8		10%	20%																										Down Good
9		30%	20%																										Down Good
10		0%	5%																										Down Bad
11		10%	20%																										Down Bad
12		0%	10%																										Down Good
13																													x
14	98%	88%																											Up Good
15		98%																											x
16		17%	27%																										Up Bad
17		55%	45%																										Down Bad
18		79%	85%																										Down Good
19		28%	28%																										x
20		90%	100%																										Down Bad
CF Red Green (3)

 

[Mustafa Evans]

I'm not sure. You should be understanding by now that a good variety of examples helps clarify. Are these the correct colours for this data. For any that are incorrect please give a detailed explanation of what colour they should be and why.

Mustafa Evans 2020-03-06 1.xlsm
	H	I	J	K																									AJ
1	Jan	Feb	Mar	Apr
2																													x
3		51%	0.61																										x
4		61%	71%																										Up Good
5		20%	10%																										Up Good
6		10%	20%																										Up Bad
7		40%	38%																										Up Bad
8		10%	20%																										Down Good
9		30%	20%																										Down Good
10		0%	5%																										Down Bad
11		10%	20%																										Down Bad
12		0%	10%																										Down Good
13																													x
14	98%	88%																											Up Good
15		98%																											x
16		17%	27%																										Up Bad
17		55%	45%																										Down Bad
18		79%	85%																										Down Good
19		28%	28%																										x
20		90%	100%																										Down Bad
CF Red Green (3)

The colours in the data as 100% correct, so sorry for the trouble.

 

[Peter_SSs]

The colours in the data as 100% correct,

Hopefully you are wrong about that as row 14 appears to be the same as row 5 so should be red too?

Mustafa Evans 2020-03-06 1.xlsm
	F	G	H	I	J	K																									AJ
1	Nov	Dec	Jan	Feb	Mar	Apr
2																															x
3				51%	0.61																										x
4				61%	71%																										Up Good
5				20%	10%																										Up Good
6				10%	20%																										Up Bad
7				40%	38%																										Up Bad
8				10%	20%																										Down Good
9				30%	20%																										Down Good
10				0%	5%																										Down Bad
11				10%	20%																										Down Bad
12				0%	10%																										Down Good
13																															x
14			98%	88%																											Up Good
15				98%																											x
16				17%	27%																										Up Bad
17				55%	45%																										Down Bad
18				79%	85%																										Down Good
19				28%	28%																										x
20				90%	100%																										Down Bad
CF Red Green (3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2:Q20	Expression	=AND(OR(AND(RIGHT($AJ2,3)="Bad",(G2-F2)*IF(LEFT($AJ2,2)="Up",1,-1)>=10%),AND(RIGHT($AJ2,4)="Good",(G2-F2)*IF(LEFT($AJ2,2)="Up",-1,1)>=10%)),COUNTA(G2:$Q2)=1)	text	NO
G2:Q20	Expression	=AND(OR(AND(RIGHT($AJ2,4)="Good",(G2-F2)*IF(LEFT($AJ2,2)="Up",1,-1)>=10%),AND(RIGHT($AJ2,3)="Bad",(G2-F2)*IF(LEFT($AJ2,2)="Up",-1,1)>=10%)),COUNTA(G2:$Q2)=1)	text	NO