Conditional Formatting & YTD Calculation

[rockstar1312]

Need some help. Am applying conditional formatting based on RAG rating (Red, Amber & Green) data, and it is working, but when it comes to YTD, the conditional formatting doesnt work as expected. The conditional formatting based on colours for YTD uses the YTD calculation but I want it to use the RAG criteria. It works for % YTD, where it uses average. I cannot change everything to average though.

I mean as you can see YTD on cell O2 shows 136 which is red, whereas it should be grean based on the RAG rating.


Any suggestions?

Sales

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Ref.	Sales	Target	R	A	G	Aug-16	Sep-16	Oct-16	Nov-16	Dec-16	Jan-17	Feb-17	Mar-17	YTD(AUG16-MAR17)	Traffic
2	1.00	UK	20	30+	20-30	<=20	26	27	15	10	17	18	12	11	136
3	2.00	Germany	0	>2	1-2	0	0.26	2.00	0.15	0.10	0.17	0.18	2.00	0.11	4.97
4	3.00	France	90%	<75%	75-89%	>=90%	64%	62%	81%	88%	100%	86%	86%	100%	83%
5
6
7
8
9
10

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:62px;"><col style="width:71px;"><col style="width:50px;"><col style="width:62px;"><col style="width:62px;"><col style="width:62px;"><col style="width:62px;"><col style="width:62px;"><col style="width:62px;"><col style="width:62px;"><col style="width:62px;"><col style="width:62px;"><col style="width:62px;"><col style="width:64px;"><col style="width:111px;"><col style="width:52px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula O2 =SUM(G2:N2) P2 =O2 G3 =G2/100 I3 =I2/100 J3 =J2/100 K3 =K2/100 L3 =L2/100 N3 =N2/100 O3 =SUM(G3:N3) P3 =O3 O4 =AVERAGE(G4:N4) P4 =O4 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[rockstar1312]

I tried Excel Jeanie Addin and the test and the preview posts were fine but now it lost the formatting!!