determining running counts

[phumpher]

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)

% A’s
=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 I’m trying to do?

Thanks in advance for your help.

 

[ProfessionalExcel.com]

Hi,

Instead of explaining my proposed solution, I put together a mock-up that
you can download at the following link:

http://www.professionalexcel.com/forumfiles/2009-08-21_Excel_Worksheet_Functions_01.zip

You can have a look at the forumulas I have used to calculate running daily
counts of consecutive days, taking into account days on leave. The main
lesson is to structure your data in a more managebale way, i.e. my solution
transposes the data and each employee has a column, with each row
representing a day.

I've calculated the count of consectuive days in one column, then added a
further column to make the decision on whether an employee should be paid a
bonus on that given day.

Hope this helps!


--
----------------------------
Please rate this post if it answers your question.

Thanks,

Chris
http://www.ProfessionalExcel.com

 

[phumpher]

Chris,

That’s quite slick.

I’m trying to figure out the functions that will work and you done it the
easy way by adding extra separate columns to keep track of the counts.

Thanks for the help.

It also seems that you’ve come up with a better way to determine the recent
running count, by using the count from the last day worked.

And by determining the Max() of the count I should be able to keep track of
Max number of “A†‘s.

If I add another employee to the data and then sort the employees
alphabetically by name, it appears that I just need to have an equal number
of extra separate columns, with the correct formulas copied across and down.

Thanks again.

 

[smartin]

Good Stuff, Chris!

Please don't take this as a criticism, just an observation...

The only thing that seems to be lacking--and something the OP doesn't
address--is what should happen when an employee has a long winning
streak. E.g., if an employee has straight "A"s for 99 days, they earn
the 28 day bonus, but nothing thereafter.

It might make sense if a 28 day bonus should also reset the counters,
but the OP should provide direction on this.

 

[phumpher]

Chris,

Using your ideas and changing a few things around I seem to have arrived at
a solution.

If you would like see what I came up with, you should be able to download it
here:

http://www.mediafire.com/?sharekey=86af9235331868057f7ec40ada4772a610ab319c60f3b1435be6ba49b5870170

Thanks again for the push in an easier direction.

If you see anything that can be improved, or if I’m trying to do something
doofus-like, please feel free to point it out.



smartin,

Thanks for the thoughtful comments.

It helped to push me in a direction that should allow for an easier way to
add the ability to track more bonus levels and even the possibility of a
carryover count to the next chart.

28 day streaks are exceedingly rare, and longer streaks even more so.

Fortunately, The-Bonus-Dude (Dude-ette actually) makes a significantly big
deal out of a long “A†series, so keeping track of the max in a long “Aâ€
streak should suffice to determine an employees “super bonusâ€.
If in the future, the number of “A†streaks that go longer than 28 and then
beyond the next number, say 35, increases by a reasonable amount (more than
the 2 or 3 that we have now) then I think it will be fairly easy to add a
counter for that new number.

For our use, we would not reset after 28, because each successive bonus
increases in value, so we want to keep track of how long a series continues.
Perhaps in the future we will need to add more levels of bonus to the five
(7, 14, 21, 28, and max) that we already have.
Who knows, maybe this spreadsheet will make it easier to see what amount of
bonuses are being paid out and our whole bonus program will change.

 

[ProfessionalExcel.com]

smartin,

Good spot on the bonuses after 28 days. For completeness I've modified a
second version so that it uses the maximum consecutive days trigger to
restart the first bonus:

http://www.professionalexcel.com/forumfiles/2009-08-21_Excel_Worksheet_Functions_01v2.zip

Whether this makes practical sense, I won't start that debate. I expect you
have to draw the line somewhere on having a max days bonus, then reverting
back to the start. I mean, at some point I would have thought a promotion is
required for 1000s of A days, not just a bonus!

phumpher,

I'm glad you found my comments useful. I've had a quick look at your
solution and it seems ok. Interesting technique using the decimal addition to
avoid counting duplicates. I think your safe in the assumption that no one
will get 1000 'A' days in a row to ruin your calculations ;o)

Regards,

--
----------------------------
Please rate this post if it answers your question.

Thanks,

Chris
http://www.ProfessionalExcel.com