trying to create a formula for rules and color coding

Hey all. Just joined and this is hopefully an easy question. I have an excel table that calculates sales amounts of different people but the amount of people I have on a given day is different. I want the cells to shade green if the store met goals that day and red if it falls below. Each person has a sales goal of $500.

I am currently using the formula =SUM(B2:B16)/MAX (1, COUNT(B2:B16)-COUNTIF(B2:B16,0)) so that the averages exclude empty cells for when certain sales people aren't there. Additionally it makes the rule in that cell easy as greater than $500 shades green and less than $500 shades red.

The problem is that the store total won't shade properly on many days. Most of the time I have 8 people working the store so naturally $4000+ is a good day and would shade green. Sometimes though I have 6, 9, etc so it skews the math. 9 people selling $480 puts us well over the $4000 mark but each individual missed individual goals. Conversely 5 sales people at $570 meets individual goals AND store goals but is less than $4000 so the individual's cells shade green but the store stays red but I would want it to be green.

What formula do I need for this or do I need a macro or what?

http://imgur.com/kCcfNtC

If you look at cells b20:c21 you'll see what I mean. Cell b21 is shaded green because above $500 is a great day for our average per person. b20 is shaded wrong though because if we hit per salesperson goals then the store did well. Similarly c20 is wrong because we didn't hit per salesperson goals but we had more total sales then on the day with b20. I don't know how to make the cell rule only consider how many reps there are on a given day for color shading. Right now the rule is just below 4000 and above 4000. I don't want to have to manually enter a different number for the rule by each day but that is all I can think to do. There has to be a robust way to do this right?