Conditional Formating with percentages increase

[dharma710]

Hi,

I need to analise the following:

Average "week 26" "week 27" "week 28"
13 24 14 23


Then I need to make a conditional format like this for every week:

Red: > 20 % increase of the Average
Yellow: > 10 % and <= 20 % increase of the Average
Green: <= 10 % increase of the Average


Can you help me please?

[ChemistB]

Entered in this order (they'll appear in the reverse order), using formula
Red =(B2-$A2)/$A2>20%
Yellow = AND(ISNUMBER(B2),(B2-$A2)/$A2<=20%)
Green = AND(ISNUMBER(B2),(B2-$A2)/$A2<=10%)
Manage rules and Check "Stop if true"

CondFormat.jpg

[dharma710]

Hi,

Thanks! It worked perfectly!
I have one more doubt.

What about if instead oh numbers it was %, like this:

Average "week 26" "week 27" "week 28"
13% 24% 14% 23%


And I would like to see this colours:
Red: > 10 % variation
Yellow: > 2 % and <= 10 % variation
Green: <= 2 % variation

---------- Post added at 06:27 AM ---------- Previous post was at 04:27 AM ----------

Hi,

Also I'm triyng to change the first conditional formating to this case:
Average "week 26" "week 27" "week 28"
13% 24% 14% 23%

Red: > 10 % decrease
Yellow: > 0 % and <= 10 % decrease
Green: <= 0 % decrease


If you can explain to me the diference to the first configuration, please, i'll be glad to learn.

[ChemistB]

Average "week 26" "week 27" "week 28"
13% 24% 14% 23%

And I would like to see this colours:
Red: > 10 % variation
Yellow: > 2 % and <= 10 % variation
Green: <= 2 % variation

I'm not sure what you mean by 10% variation. For the example given, Wk 26 would be red,27 green and 28 red? Instead of

Please Login or Register  to view this content.

use

Please Login or Register  to view this content.

[dharma710]

That last thing didn't worked.

I'm going to explain the 2 diferente cases:

1st:
----------------------------------------------------------------------
Fixed..week 26....week 27....week 28
95%.......94%........92%.......4%


I need to make a conditional format like this for every week comparing with "Fixed" column:

Red: > 10 % variation
Yellow: > 2 % and <= 10 % variation
Green: <= 2 % variation


----------------------------------------------------------------------



2nd:
----------------------------------------------------------------------
Fixed..week 26....week 27....week 28
95%.......94%........92%.......4%


I need to make a conditional format like this for every week comparing with "Fixed" column:

Red: > 10 % decrease
Yellow: > 0 % and <= 10 % decrease
Green: <= 0 % decrease

----------------------------------------------------------------------


Can you please help me with this 2 distint cases?

[ChemistB]

Examples work wonders. So if your fixed is 95%
Option 1
Red: > 10 % variation ie. values <85 or greater than 105?
Yellow: > 2 % and <= 10 % variation values between 85 and 105? (excluding below)
Green: <= 2 % variation values between 93 and 97%?

Option 2
Red: > 10 % decrease <85%
Yellow: > 0 % and <= 10 % decrease 85 to 95
Green: <= 0 % decrease >= 95%
Is that all correct?

[dharma710]

Yes, it is that.

[ChemistB]

Take a look at this spreadsheet. I set up the conditional formatting for both options you presented. Let me know if you have any questions.

[dharma710]

Hello,

1st case worked perfectly!
But 2nd case, didn't work the yellow one. Can you see if every formula it's correct please?

[ChemistB]

Hi dh,
Doesn't work in your spreadsheet or doesn't work on the example I posted? When I look at it, wk 27 and 28 at 10 and 14% show as yellow. If it's on your spreadsheet, make sure that your conditions are in the same order as mine and that they are checked for Stop if true.

[dharma710]

It's in my spreadsheet.
For example, in mine I have this numbers:
Fixed..week 26....week 27....week 28
100%.......97%........25%.......100%

With this rules:
Red: > 10 % decrease
Yellow: > 0 % and <= 10 % decrease
Green: <= 0 % decrease


In the week 26, I have a decrease of 3%, so that cell should be yellow, but it isn't.

[ChemistB]

So take a good look at how I set up the rules in mine. Are you're in the same order? are the checkboxes checked? If you can't find a descrepancy, you'll need to upload the sheet (Go Advanced>Manage Attachments)