Conditional Formatting Help

[EvansB2]

Hi,

I'm looking to apply some simple conditions to return a typical Red, Amber & Green (RAG) analysis.

The 3 conditions are set out below the table. The problem one is the Amber condition as it has two metrics and this is the one that I struggled to incorporate into a Conditional Formatting formula.

Any help would be appreciated.

Ben

[TABLE="width: 398"]
<colgroup><col width="29" style="width: 22pt; mso-width-source: userset; mso-width-alt: 1060;"> <col width="58" style="width: 44pt; mso-width-source: userset; mso-width-alt: 2121;" span="2"> <col width="64" style="width: 48pt;" span="6"> <tbody>[TR]
[TD="width: 29, bgcolor: transparent"] [/TD]
[TD="width: 58, bgcolor: #D9D9D9"]A[/TD]
[TD="width: 58, bgcolor: #D9D9D9"]B[/TD]
[TD="width: 64, bgcolor: #D9D9D9"]C[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]1[/TD]
[TD="width: 58, bgcolor: #B4C6E7"]Average[/TD]
[TD="width: 58, bgcolor: #B4C6E7"]Score[/TD]
[TD="width: 64, bgcolor: #B4C6E7"]Diff[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]2[/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"]50[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=92D050]#92D050[/URL] , align: right"]-100.0%[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]3[/TD]
[TD="bgcolor: transparent"]70[/TD]
[TD="bgcolor: transparent"]80[/TD]
[TD="bgcolor: red, align: right"]12.5%[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]4[/TD]
[TD="bgcolor: transparent"]150[/TD]
[TD="bgcolor: transparent"]140[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFC000]#FFC000[/URL] , align: right"]-7.1%[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]5[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]6[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: #D9D9D9"]7[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 5"]If Score is greater than 10% above the Average = Red[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 8"]If Score is less than 10% above Average or less than 10% below the Average = Amber[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 5"]If Score is greater than 10% below the Average = Green[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Caleeco]

Its important to remember that '100%' in Conditional Formatting land is represented as '1'.

So if you wanted 10% above or below the average. You could take the Absolute value of the %, and check is it less than or equal to 0.1 (10%).

Amber

=ABS($C2)<=0.1

Hope that helps
Cheers

Caleeco

 

[EvansB2]

Thanks Caleeco,

Could you sugest a 'Rule Type' in Conditional Formatting for this code?

Ben

 

[Fluff]

Select "New Rule" & then "Use a formula"

 

[EvansB2]

Done it! Works a treat.

Thanks

Ben

 

[Fluff]

Glad we could help & thanks for the feedback