Conditional Formatting to show progress against a target

[shauncasey]

Hi Guys,

I have a spreadsheet that has the below information;

A1 - Target Revenue For Month

B2 - Daily Revenue
C2 - MTD Revenue

What I want is conditional formatting (red/green) to show whether the MTD Revenue is on target to achieve the Target Revenue For Month. For example, the Target Revenue For Month will be £1000, and this divided by 30 days = £33.33 per day. The conditional formatting will show if still on target (achieving more that £33.33 per day on average).

Hope this is clear?

Thanks in advance

 

[DRSteele]

hold please

 

[DRSteele]

OK, I think I have it now.

Select C3 to C9. Execute Conditional formatting|New rule|Use Formula..|type =C3>=$A$2/30*COUNT($C$3:C3) into the Rule box|set format to Fill_Green; create a second rule by typing =C3<$A$2/30*COUNT($C$3:C3) into the Rule box|set format to Fill_Red. [N.B. the colours won't show here but they will on your spreadsheet.]

Excel 2012
	A	B	C
1	target rev	daily rev	MTD rev
2	$ 1,000.00
3		35	35
4		39	74
5		21	95
6		19	114
7		36	150
8		45	195
9		46	241
Sheet33

Cell Formulas
Range	Formula
C3	=B3
C4	=B4+C3
C5	=B5+C4
C6	=B6+C5
C7	=B7+C6
C8	=B8+C7
C9	=B9+C8