Moving Average
 

Hi everyone,

I have been given a project to do but am a little stumped and hoping someone could give me some assistance/advice.

We have several sites and each site is allowed to make up to 1.5% of errors. As each has various amount of employees, I want to focus on one at the time.

Now this one particular site has 600 employees and they can make up to 1.5% of errors each month otherwise there site bonuses are effected.
For example, in a month, this site with 600 employees can make 9 errors max which would equate to 1.5%.

Is it possible to create a table that will reduce the number of errors allowed each week. For example, column B is the average amount of errors they can make each week but in Column C, if they made 4 errors in Week 1, the rest of the week would automatically reduce but still cap the total at 9.

A B C
Staff 600
Week1 2 4
Week2 2 2
Week3 2 2
Week4 2 1
Week5 1 0

9 9

I think this is just the starting point to my project but hopefully this will help a lot.

Any help would be much appreciated.

Pri