determining running counts
Hi,
I'm having some trouble figuring out how to keep a running count of some data.
The data:
2009|6/01|6/02|6/03|6/04|6/05|6/06|6/07|6/08|6/09|6/10|6/11|6/12|...and
continues for several months
Emp1| | | A | A | A | | | A | A | A | A | A |...
Emp2| A | P | A | | A | A | A | A | A | | P | A |...
Emp3| A | | | P | A | A | P | | | A | A | P |...
Emp4| A | A | | A | P | A | P | A | | A | A | A |...
and continues for about 40 employees
Employees receive a letter grade for their performance each day they work.
Originally it was supposed to be "A","B","C","D" for varying levels of good
and "F" for poor performance.
In practice it has turned out to be either an "A" for good or a "P" for poor
for each day worked. It they don't work on a particular day, then there is no
entry.
Bonuses are paid to employees who receive 7 "A" grades in a
row(consecutive?, but need not be contiguous, as, if an employee worked 3
days and received "A" grades and then was off work for 2 days and then worked
5 days with "A" grades that world be a total of 8 "A" grades and qualify for
the bonus), a series of 7 "A" grades.
Additional bonuses are also paid at 14, 21, and 28 intervals.
Receiving a P grades resets the count for bonuses to 0.
Emp1 count=8
Emp2 count=1
Emp3 count=0
Emp4 count=4
If an employee has several series of 7 A grades each interrupted by a P
grade then they would receive several 7-day bonuses.
If an employee had a series of 15 A grades and then a P, they would
receive a 7-day bonus and a 14-day bonus.
If an employee had a series of 20 A grades and then a P on their 21st
day, they would receive a 7-day bonus and a 14-day bonus. Ouch! Missed out on
the next big bonus by that much!
In trying to track some of the data I have been able to determine:
total Poor or P grades {totalPs}
=COUNTIF(B1:CZ1,"P")
total number of days worked {totalDaysWorked}
=COUNTA(B1:CZ1)
% As
=1-{totalPs}/{totalDaysWorked}
recent running count
{bigString1}=CONCATENATE(CZ1,CY1,CX1,¦,¦,D1,C1 ,B1)
=IF(ISERROR(SEARCH("p",{bigString1})-1),LEN({bigString1}),SEARCH("p",{bigString1})-1)
But I have not been able to figure out how to determine:
the number of groups of 7 A's
and thus 14, 21, or 28
And I would like to determine the Max number of A s.
Suppose an employee worked 60 or 70 days in the last few months and received
a P in the first week but then did well and had 38 A s before the next
P grade and has had a combination of A and P in the last few weeks, and
currently has a series of 9 A grades.
How do I determine and keep track of that series of 38?
Are there easier ways to do any of the things Im trying to do?
Thanks in advance for your help.
|