Writing a program with changing variables. . .

Hopefully I can explain this to where it makes sense.

I've written a program that takes sales numbers in 2 different departments and applies a + and - value by awarding and taking away a point to the department that exceeds and falls short of the other department in different areas. Each department has earned a variable that when used could would increase the following days point potential.


Day 1 has a potential of 2 points. 1 point is awarded to the department w/ salesman having the most sales. 1 point for department with the higest total sales. If one department earns a point the other department will earn a neg point. .

Example: Department A salesman W has 4 sales and salesman X has 3 sales total 7 sales. Department B salesman Y 3 sales, salesman Z has 2 sales, total 5.

Deparment A receives 1 point because Salesman W had the most individual sales (4) and 1 point for having the most total sales. (7) Department B is awarded a neg. -1 for team not having a salesman tie or beat Salesman W, and a neg -1 For having fewer total sales than Dept. A

End of Day 1 Dept. A +2 Dept. B -2

The next will have one extra variable mixed in based upon performance the previous month. Let's say Dept. A received 3 points on a Bonus Card and Dept B received 2 points on a Bonus Card. The Bonus Card automatically adds 1 point to each possibility for points at the start of the next day. The Bonus card is used every day a team is behind and the team still has points left on their Bonus Card. Each time a bonus card is played, the team's bonus card will have their bonus total reduce by 1.

Day:2.......... now has the pontential to earn a total of 4 points. Since Department B is behind and they have 2 Points on their Bonus Card, the highest number of sales by an individual for Day 2 will earn 2 pts for his Department, and team with the most total sales for day 2 will earn 2 points for their department. Dept. A still has 3 points on their Bonus Card, Dept. B now has 1 point taken off their Bonus Card giving the 1 point remaining on their bonus card.

Department A salesman W makes 4 sales and salesman X makes 2 sales for a total of 6.
Department B salesman X makes 3 sales and salesman y makes 3 sales for a total of 6.
Department A would earn 2 points for having the salesman with the most sales, Dept. B would lose 2 pts. Department A gets 0 points for tying Dept. B and vice versa, giving Dept A a total of 4 points at the end of Day 2, Department B - 4 pts.


Day 3.............has a total of 6 earning points available. Since Dept. B is behind in total points and still has 1 Bonus point remaining on their Bonus Card, highest individual sales earns 3 points, highest total sales earns 3 points. Department A still has 3 points left on their Bonus card, Department B has 0 points left on their Bonus card.

Department A Salesman W has 2 sales and Salesman X has 3 sales. Department B's salesman Y has 4 sales and Salesman Z has 2 sales. Deartment B will earn 3 points for having the individual with the most sales and 3 points for having the most total sales. Giving them a total of 6. Department A loses a total of 6 points. At the end of day 3 Department A is now -2 and Department B is +2.

Day 4.....Since Team A is behind and has 3 points on their bonus card, each potential point opportunity increases by 1. Team with the highest sales by individual will receive 4 points and most total sales receives 4 points. Team A has 2 points left on their bonus card, Department B has 0 points on their Bonus card.........................................

I've written a program that adds and subtracts the point totals for each day and currently I am manually entering a value for the Bonus Card.
For example:

Day 1 formula for Ind. sales
F22: =IF(F8<F19,1,IF(F8=F19,0,IF(F8>F19,-1)))

Day 1 Total sales.
F23: =IF(F9<F20,1,IF(F9=F20,0,IF(F9>F20,-1)))

Dept. A point Total For Day 1
F24: =SUM(F22,F23)
F25: = F24*-1 For Department B total points

Cell D8 has Dept A Beginning Bonus Card Total. Cell D19 has Department B's Beginning Bonus Card number.

I need to write a formula that applies Cell D8 and D19, the Bonus Card points in the manner I described above. Adding to the next day's earning potential when applicable and reducing the number of Bonus points each time a department uses one.