Live conditional formatting

[liam_crad]

I have set up a spreadsheet to track sales at work and have weekly and monthly targets. 4 sales a week - 16 every 4 weeks essentailly. I can format the weekly cells to change colour depending on how many sales each person achieves. 4 or more = Green, 3 = Amber, 2 or less = Red. I want to monthly column to update live so if after 2 weeks a member of the team has 9 sales the cell would light up green. If after 3 weeks the member of staff had still only achieved 9 sales I want the cell to change to amber once I have filled in the weeks 3 cell as '0'.

I wasn't sure If I could do a formula where any totally blank cells are not included and the cell changes based on the number of cells actually completed?

Any thoughts would be greatly appreciated.

Liam

 

[gnrboyd]

I'm not exactly sure how you have your sheet set up but here is one example that shows how you could use the AVERAGE formula in your conditional formatting. It will factor in zero values into the average. In cell F2, here is the first argument (green color):

=AVERAGE($B2:$E2)>=4

You will need to add the others for amber and red depending upon how you want your results. Hope this helps.

Book2
	A	B	C	D	E	F
1	Name	Week1	Week2	Week3	Week4	Monthly
2	Bob	4	4			8
3	Sandy	4	4	0		8
4	Joe	4	4	0	0	8
Sheet1

 

[liam_crad]

Thanks for reply, when putting that formula in it gives me a TRUE or FALSE and no figure in the end column anymore. For now I've got it as the correct colours for the end of the month so will have to do for now until I become a bit more advanced.

 

[gnrboyd]

Thanks for reply, when putting that formula in it gives me a TRUE or FALSE and no figure in the end column anymore. For now I've got it as the correct colours for the end of the month so will have to do for now until I become a bit more advanced.

I think you misunderstood... This formula :

=AVERAGE($B2:$E2)>=4

needs to be used for one of the conditions in conditional formatting in column F as you say you have done with the other cells. (You would need to add the others for the other colors as appropriate.) The actual formulas in column F just needs to be the simple sum formula as shown in my example. To summarize, you will have a regular sum formula in column F as well as conditional formatting in the same column to control the colors as you requested. Make sense?