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

	A	B	C
1	Average	Score	Diff
2	100	50	-100.0%
3	70	80	12.5%
4	150	140	-7.1%
5
6
7
	If Score is greater than 10% above the Average = Red
	If Score is less than 10% above Average or less than 10% below the Average = Amber
	If Score is greater than 10% below the Average = Green

<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> </tbody>

 

[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