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

 

[6StringJazzer]

You need two conditional formatting rules. Apply each rule to the column you want to change color. This assumes your data starts in row 2; if not, change the 2.

=RIGHT($AJ2,4)="GOOD"
set format to green

=RIGHT($AJ2,3)="BAD"
set format to red

 

[Mustafa Evans]

Thanks for the reply however both conditions are required for the cell to change colour.

There has to have been a 10% increase or decrease from the previous month and the key needs to correspond with the condition of the change being good or bad.

 

[Peter_SSs]

Can you show us a small set of sample dummy data and explain the expected results in relation to that sample? XL2BB

I also suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.

 

[Mustafa Evans]

Hi, I couldn't use the XL2BB on my work PC. However, I have update preferences and have attached some screenshots of data.

The formula for GREEN is

=IF(AND(OR(AJ3="UP GOOD",AJ3="DOWN GOOD"),I3-H3>=10%),1,0)

and the formula for RED is

=IF(AND(OR(AJ3="UP BAD",AJ3="DOWN BAD"),I3-H3<=10%),1,0)

Cell I5 seems to work but when copied down it doesn't any longer, see I16 for example this should be white and not red.

Hope you can help.

 

[Peter_SSs]

The formula for GREEN is

=IF(AND(OR(AJ3="UP GOOD",AJ3="DOWN GOOD"),I3-H3>=10%),1,0)

That seems to be indicating that "UP GOOD" and "DOWN GOOD are exactly the same. Is that so?

Could we have example rows that demonstrate all 4 of:
UP GOOD
DOWN GOOD
UP BAD
DOWN BAD

Also, are you really wanting this CF to apply to whichever of columns G or H or I or J or ... is the last one with data? If so, what is the last 'Month' column in your hidden section?

 

[Mustafa Evans]

That seems to be indicating that "UP GOOD" and "DOWN GOOD are exactly the same. Is that so?

Could we have example rows that demonstrate all 4 of:
UP GOOD
DOWN GOOD
UP BAD
DOWN BAD

Also, are you really wanting this CF to apply to whichever of columns G or H or I or J or ... is the last one with data? If so, what is the last 'Month' column in your hidden section?

I was trying to use the AND OR IF functions to reduce CF formulas. Basically, we report on the previous month and compare to the previous month, so in this instance we will be reporting on February and comparing with January, there is nothing in the hidden columns, it's just I have put the Key in column AJ in all workbooks.

Each individual reporting statement might increase or decrease monthly and going up or down might be good or bad, hence being green or red.

 

[Peter_SSs]

1. You are going to write and apply a new CF formula every month?

2. ...

Could we have example rows that demonstrate all 4 of:
UP GOOD
DOWN GOOD
UP BAD
DOWN BAD

 

[Mustafa Evans]

You are going to write and apply a new CF formula every month?

Yes, well to be honest will only be showing the last 3 month months as in the example and hiding everything else, so for this month will be showing Dec, Jan and Feb.

 

[Peter_SSs]

Yes, well to be honest will only be showing the last 3 month months as in the example and hiding everything else, so for this month will be showing Dec, Jan and Feb.

OK, well I think a single CF formula (for each colour) should 'probably' be able to cope with all months. But I would still like my other issue addressed before offering a suggestion.