Exclude cells from 3 point colour scales based on text in another cell.

[MartinHill]

Hey everyone,

In column 'G' I have set up a 3 point colour scale based on the date. I want some of these cells to be excluded from this calculation based on the corresponding cell in column 'E'.

So the rule I'm trying to create should go something like this:
If E5 = "Action Taken" Exclude G5 from 3 point scale

I'm not quite sure how to accomplish this so any help would be greatly appreciated.

 

[FDibbins]

pics don't always come through well - I am unable to view the 1 you posted

In column 'G' I have set up a 3 point colour scale based on the date.

Can you explain what/how you did this?
can you explain or show what your data looks like, and what your expected outcome would look like?

 

[MartinHill]

Thanks for the reply!

Can you explain what/how you did this?

I set up the colour scale by selecting column G -> Home tab -> Conditional Formatting -> Colour Scales -> Green-Yellow-Red Scale (1st option)

can you explain or show what your data looks like, and what your expected outcome would look like?

This is what it currently looks like (all of column G formatted based on the Green-Yellow-Red scale):
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Column E (Status)[/TD]
[TD]Column G (Date last Actioned)[/TD]
[/TR]
[TR]
[TD]Contacted[/TD]
[TD]18/08/2016 (Background Formatted Green)[/TD]
[/TR]
[TR]
[TD]Replied[/TD]
[TD]17/08/2016 (Background Formatted Yellow-Green)[/TD]
[/TR]
[TR]
[TD]Action Taken[/TD]
[TD]15/08/2016 (Background Formated Red)[/TD]
[/TR]
[TR]
[TD]Contacted[/TD]
[TD]16/08/2016 (Background Formated Orange-Yellow)[/TD]
[/TR]
</tbody>[/TABLE]

This is what I want it to look like (excluding the 3rd row with its status marked as "Action Taken"):
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Column E (Status)[/TD]
[TD]Column G (Date last Actioned)[/TD]
[/TR]
[TR]
[TD]Contacted[/TD]
[TD]18/08/2016 (Background Formatted Green)[/TD]
[/TR]
[TR]
[TD]Replied[/TD]
[TD]17/08/2016 (Background Formatted Yellow)[/TD]
[/TR]
[TR]
[TD]Action Taken[/TD]
[TD]15/08/2016 (Background Formated White)[/TD]
[/TR]
[TR]
[TD]Contacted[/TD]
[TD]16/08/2016 (Background Formated Red)

[/TD]
[/TR]
</tbody>[/TABLE]

Hope this makes a bit more sense. Let me know if you have any more questions.

 

[Green Crocodile]

You would need to create a second conditional formatting and apply it to column G. You must click the "use formula to determine cells"

The formula would be =$E1="Action Taken", then format it to how it should look normally (no fill color, black font, etc).

Then go to conditional formating > manage rules > Make sure that new condition rests above the three color scale, then check Stop If True.

 

[MartinHill]

Thanks for the advice. It almost works but not quite. :/

This is what it looks like now (It formats the row marked with 'Action Taken' correctly but doesn't adjust the colour scale - It is still accounting for that date):
[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD]Column E (Status)[/TD]
[TD]Column G (Date last Actioned)[/TD]
[/TR]
[TR]
[TD]Contacted[/TD]
[TD]18/08/2016 (Background Formatted Green)[/TD]
[/TR]
[TR]
[TD]Replied[/TD]
[TD]17/08/2016 (Background Formatted Yellow-Green)[/TD]
[/TR]
[TR]
[TD]Action Taken[/TD]
[TD]15/08/2016 (Background Formated White)[/TD]
[/TR]
[TR]
[TD]Contacted[/TD]
[TD]16/08/2016 (Background Formated Orange-Yellow)[/TD]
[/TR]
</tbody>[/TABLE]

 

[Green Crocodile]

There might be a better way, but this will work. You will need a two helper columns (You can hide it later if you want). For this post I will assume it's column H and I (Any unused column will work).

Steps:
1) In column F this formula =IF(E1 < > "Action Taken",G1,"") - Then double click to copy it down to the end of the list.
2) In cell I1 put =IF(COUNT(H:H) > 0,MAX(H:H)) - In cell I2 put =IF(COUNT(H:H) > 0,MIN(H:H)) - In cell I3 put =MEDIAN(I2,I1)
3) Go to manage condition rules and double click on the one for the three color scale.
4) Change the Type to Formula for all three (Minimum, Midpoint, and Maximum).
5) Formula for Min would be =$I$2 - Formula for Midpoint will be =$I$3 and formula for Maximum will be =$I$1

This should do the trick. It's not ideal, but at least you will get the results you need. Remember that you can always hide these two columns so it doesn't change the look of your worksheet.

 

[MartinHill]

Awesome, thanks! Worked a treat. I just hid the helper columns on another sheet


Just for anyone's future reference:

1) In column F this formula =IF(E1 < > "Action Taken",G1,"") - Then double click to copy it down to the end of the list.

He meant column H, not F.

Also, one improvement for this formula to ignore blank rows use =IF(G1 = 0, "", IF(E1<>"Action Taken",G1,""))

Thanks again!