Conditional formating

[EleanorSykes1989]

Hi all,

I'm having an issue with conditional formatting, hope you can help. I'm doing some work on the gender pay gap, and need to use Red, Amber & Green to indicate good, ok, and bad.

For example if the % of males and females to receive a bonus is below; I need to show the values in green, as the gap between the 2 is low.


% of males to receive bonus = 10
% of females to receive bonus = 8

so basically..

if the difference = 0 - 10 = show green
if the difference = 10.1 - 15 = show amber
if the difference = 15.1 - 100 show red

Is there a way I can do this please?
Many thanks

[TABLE="width: 253"]
<colgroup><col width="217" style="width: 163pt; mso-width-source: userset; mso-width-alt: 7936;"> <col width="120" style="width: 90pt; mso-width-source: userset; mso-width-alt: 4388;"> <tbody>[TR]
[TD="class: xl65, width: 217, bgcolor: #DBDBDB"][/TD]
[TD="class: xl66, width: 120, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: #DBDBDB"][/TD]
[TD="class: xl67, bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

You can use Conditional Formatting Formulas to do this.
Let's say that your values are in cells A2 and B2. Then the formulas might look something like this:

if the difference = 0 - 10 = show green
=ABS(A2-B2)<=10%

if the difference = 10.1 - 15 = show amber
=AND(ABS(A2-B2)>10%,ABS(A2-B2)<=15%)

if the difference = 15.1 - 100 = show amber
=AND(ABS(A2-B2)>15%,ABS(A2-B2)<=100%)

 

[Gerald Higgins]

Yes, this is your basic conditional formating.

You probably need to setup two conditions.

First, make the default format green.
Then, set up an amber condition to apply when the difference is greater than 10, and a second red condition to apply when the difference is greater than 15.
Make sure the conditions are prioritised so that the red condition is checked first.

All of this is do-able in the conditional formating dialog box, post back for more details if required.

 

[Gerald Higgins]

Joe4 - so long as you get the sequencing of the conditions correct, you can simplify your formulas to something like

if the difference = 10.1 - 15 = show amber
=ABS(A2-B2)>10%

 

[Joe4]

Yes, I know (and as long as you use the "Stop IF True" setting).
Well, I suppose you could do it without, if you put them in the right order, but that can be confusing as Excel puts them in the reverse order (the last one you enter is the first one on the list, unless you then re-order them).
To avoid any confusion and to make sure it is working as it should, I prefer to explicitly list the exact conditions. Less chance for error or unexpected results.

 

[Gerald Higgins]

Joe4 - fair enough.

I think I'd prefer not to explicitly list the exact conditions in that way, as in my opinion it's easy to introduce errors.
For example in this very minor variation, values of exactly 15% will not be handled correctly.

if the difference = 10.1 - 15 = show amber
=AND(ABS(A2-B2)>10%,ABS(A2-B2)<15%)

if the difference = 15.1 - 100 = show amber
=AND(ABS(A2-B2)>15%,ABS(A2-B2)<=100%)

It also means that, even if you set up the conditions correctly, if you later decide to change the cutoff, to, say, 20%, then you need to edit two conditions, instead of just one with my suggestion.

But I accept that if you did it the way suggested in post#2, that should work correctly.

I guess to some extent it comes down to personal preference, and also makes the point that in Excel there are often lots of ways to achieve the same thing.

 

[Joe4]

I find that newbies often find the Ordering confusing, and it is harder for us to validate what ordering/settings that they may have elected (as we cannot see that).
But we can explicitly tell them which formulas to use!
Of course, any typos in the formulas (or incorrect logic) will lead to intended results, but that is pretty much true about anything!

So I guess you could see that there are potential pitfalls either way you go, if you don't do them right!

Its good that we both posted the different ways it could be done here, so they can see the different options.
(I must say, I do find it a bit annoying that Excel puts the different Conditions in in the reverse order you enter them - that took some getting used to!).