Conditionnal Formating with IF, AND, and multiples conditions

[revolverpow]

Hi All,

I can't get my head around this.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A-B-C Store[/TD]
[TD]%Conversion[/TD]
[/TR]
[TR]
[TD]A
[/TD]
[TD]9,2%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]7,50%[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]9,0[/TD]
[/TR]
</tbody>[/TABLE]

So I have my A-B-C store and the expectation are different for all of them in terms of condition.
Right now, I'm doing manually, but I'd like it to be automated.
So when I enter the %conversion, I want it to switch between red-yellow-green depending if it meets the targets.
My problem if having A-B-C store with different expectations and I really don't know how to apply a conditionnal formatting, considering if it's an A-B-C store AND considering the %conversion.


Here are my expectation for A-B-C; the X represent the value in %Conversion column.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]RED [/TD]
[TD]YELLOW[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X>9%[/TD]
[TD]9%=>X<11%[/TD]
[TD]11%=>X[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]X>9%[/TD]
[TD]9%=>X<11%[/TD]
[TD]11%=>X[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]X>9%[/TD]
[TD]9%=>X<11%[/TD]
[TD]11%=>X[/TD]
[/TR]
</tbody>[/TABLE]


Can someone help me build the right formula for this ?!

Thank you all (and sorry for the bad english) !

 

[Noodleski]

I eh... don't see a difference between the thresholds (expectations) for A, B or C to change colours?
Limits are 9% and 11% regardless of the store?

Also, it would be easier if you could tell us what cells your data are in, so we work out a better fitting formula for you. Now, it would simply be generic.

 

[revolverpow]

My bad !!! I forgot to change the expectation
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]RED[/TD]
[TD]YELLOW[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X>9%[/TD]
[TD]9%=>X<11%[/TD]
[TD]11%=>X[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]X>8%[/TD]
[TD]8%=>X<10%[/TD]
[TD]10%=>X[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]X>6,5%[/TD]
[TD]6,5%=>X<8%[/TD]
[TD]8%=>X[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

Since the colours are the same for all three (see end of your post) the stores are irrelevant
Your colour results are also wrong. Your > and < are round the wrong way.

RED X > 9% is ok
YELLOW 9%>=X<11% is not ok. 9%>=X means X<=9% I dont think this is what you want
GREEN 11%=>X is the same as X<=11% which is the same as YELLOWs X<=11% so what colour should it be YELLOW or GREEN.

Fix these deifinitions first and I'll come back and take a look

 

[revolverpow]

**** I'm lost today !!! I didn't use the right ''<'' for GREEN expectation !!!

Should be all good now hahaha.
[TABLE="class: cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]RED[/TD]
[TD]YELLOW[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X>9%[/TD]
[TD]9%=>X<11%[/TD]
[TD]11%=<X[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]X>8%[/TD]
[TD]8%=>X<10%[/TD]
[TD]10%=<X[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]X>6,5%[/TD]
[TD]6,5%=>X<8%[/TD]
[TD]8%=<X[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fishboy]

Hi All,

I can't get my head around this.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A-B-C Store[/TD]
[TD]%Conversion[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]9,2%[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]7,50%[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]9,0[/TD]
[/TR]
</tbody>[/TABLE]

So I have my A-B-C store and the expectation are different for all of them in terms of condition.
Right now, I'm doing manually, but I'd like it to be automated.
So when I enter the %conversion, I want it to switch between red-yellow-green depending if it meets the targets.
My problem if having A-B-C store with different expectations and I really don't know how to apply a conditionnal formatting, considering if it's an A-B-C store AND considering the %conversion.


Here are my expectation for A-B-C; the X represent the value in %Conversion column.
[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]RED [/TD]
[TD]YELLOW[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X>9%[/TD]
[TD]9%=>X<11%[/TD]
[TD]11%=>X[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]X>9%[/TD]
[TD]9%=>X<11%[/TD]
[TD]11%=>X[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]X>9%[/TD]
[TD]9%=>X<11%[/TD]
[TD]11%=>X[/TD]
[/TR]
</tbody>[/TABLE]


Can someone help me build the right formula for this ?!

Thank you all (and sorry for the bad english) !

Hi revolverpow, welcome to the boards.

As mentioned by Noodleski, your example data doesn't quite fit. Beyond A, B and C all being the same, Red says "If X is greater than 9", but yellow says "If X is equal to or greater than 9, but also less than 11". Both red and yellow are saying greater than 9 and I think this might be a typo on behalf perhaps?

Now, assuming you have a list of values in column A starting in A1, you can test out the following 3 conditional formatting rules and see how they suit your needs. Select A1 and add these rules, making sure to leave them in the order theu are show here with green at the top and red at the bottom:

Green formatting rule (greater than 11): =$A1>=11%
Yellow formatting rule (greater than 9): =$A1>9%
Red formatting rule (equal to or less than 9): =$A1<=9%

Once these are in place, with A1 still selected double-click the Format Painter button from the Home Ribbon at the top of the screen. Drag down the length of your data. Click the Format Painter button again.

 

[revolverpow]

Hi Thanks all for you reply !! First time using this board/forum so i'm still confused how to edit a reply.

Here are my A-B-C expectation (for real this time, I double checked hahaha) :

[TABLE="class: cms_table_cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]RED[/TD]
[TD]YELLOW[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X>9%[/TD]
[TD]9%=>X<11%[/TD]
[TD]11%=<X<x< td=""></x<>[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]X>8%[/TD]
[TD]8%=>X<10%[/TD]
[TD]10%=<X<x< td=""></x<>[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]X>6,5%[/TD]
[TD]6,5%=>X<8%[/TD]
[TD]8%=<X<x< td=""></x<>[/TD]
[/TR]
</tbody>[/TABLE]


Because I know it would be too easy without the A-B-C distinctions.

Thanks all again !

 

[Fishboy]

Hi Thanks all for you reply !! First time using this board/forum so i'm still confused how to edit a reply.

Here are my A-B-C expectation (for real this time, I double checked hahaha) :

[TABLE="class: cms_table_cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]RED[/TD]
[TD]YELLOW[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X>9%[/TD]
[TD]9%=>X<11%[/TD]
[TD]11%=<x<x< td=""></x<x<>[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]X>8%[/TD]
[TD]8%=>X<10%[/TD]
[TD]10%=<x<x< td=""></x<x<>[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]X>6,5%[/TD]
[TD]6,5%=>X<8%[/TD]
[TD]8%=<x<x< td=""></x<x<>[/TD]
[/TR]
</tbody>[/TABLE]


Because I know it would be too easy without the A-B-C distinctions.

Thanks all again !

Are you sure you haven't got > and < mixed up for red?

 

[revolverpow]

Are you sure you haven't got > and < mixed up for red?

Yeah ! Sorry !! I don't know why I can't manage to post the board properly !!!!


[TABLE="class: cms_table_cms_table_cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]RED[/TD]
[TD]YELLOW[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X<9%[/TD]
[TD]9%=>X<11%[/TD]
[TD]11%=<X<x<x< td=""></x<x<>[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]X<8%[/TD]
[TD]8%=>X<10%[/TD]
[TD]10%=<X<x<x< td=""></x<x<>[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]X<6,5%[/TD]
[TD]6,5%=>X<8%[/TD]
[TD]8%=<X<x<x< td=""></x<x<>[/TD]
[/TR]
</tbody>[/TABLE]

Red =Under the Value
Yellow = between values
Green = over value

 

[Fishboy]

Yeah ! Sorry !! I don't know why I can't manage to post the board properly !!!!


[TABLE="class: cms_table_cms_table_cms_table_cms_table, width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]RED[/TD]
[TD]YELLOW[/TD]
[TD]GREEN[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]X<9%[/TD]
[TD]9%=>X<11%[/TD]
[TD]11%=<x<x<x< td=""></x<x<x<>[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]X<8%[/TD]
[TD]8%=>X<10%[/TD]
[TD]10%=<x<x<x< td=""></x<x<x<>[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]X<6,5%[/TD]
[TD]6,5%=>X<8%[/TD]
[TD]8%=<x<x<x< td=""></x<x<x<>[/TD]
[/TR]
</tbody>[/TABLE]

Red =Under the Value
Yellow = between values
Green = over value

Sorry revolverpow, but where I have been ill the last few days I haven't been checking the forum, so I am sorry for the delay in my response.

If I am understanding correctly, the following cf rules could be applied to your first row of data. Once you have done that, with the same cells still selected double-click the Format Painter button from the Home Ribbon at the top of the screen. Drag down the length of your data. Click the Format Painter button again.

=AND($A2="A",$B2<9%) A - Red
=AND($A2="A",$B2>=9%,$B2<=11%) A - Yellow
=AND($A2="A",$B2>11%) A - Green


=AND($A2="B",$B2<8%) B - Red
=AND($A2="B",$B2>=8%,$B2<=10%) B - Yellow
=AND($A2="B",$B2>10%) B - Green


=AND($A2="C",$B2<6.5%) C - Red
=AND($A2="C",$B2>=6.5%,$B2<=8%) C - Yellow
=AND($A2="C",$B2>8%) C - Green