Calculate Average Number of Tasks Per day, productivity

[eddiegnz1]

We’re trying to calculate the average number of tasks per day for each employee. The goal is to measure productivity of employees. This needs to be measured everyday.
<o> </o>
So our goal is to report it as follows (for example);
<o> </o>
The average tasks per day over the last 14 days for Mike was 3.79.
Mikes average was 4.03 for the last 90 days. But this must only take into account the day on which they actually completed at least one task so that days with zero tasks are not measured into their average because they didn't work that day.
<o> </o>
The data is as follows (not actual data, example only);

<o> </o>​	A<o></o>​	B<o></o>​	C<o></o>​
1<o></o>​	Name<o></o>	Date<o></o>	Task<o></o>
2<o></o>​	Mike<o></o>	8/1/2012<o></o>​	billing<o></o>
3<o></o>​	Cindy<o></o>	8/1/2012<o></o>​	payments<o></o>
4<o></o>​	Andy<o></o>	8/1/2012<o></o>​	billing<o></o>
5<o></o>​	Mike<o></o>	8/1/2012<o></o>​	billing<o></o>
6<o></o>​	Mike<o></o>	8/1/2012<o></o>​	modify<o></o>
7<o></o>​	Cindy<o></o>	8/1/2012<o></o>​	modify<o></o>
8<o></o>​	Cindy<o></o>	8/2/2012<o></o>​	payments<o></o>
9<o></o>​	Andy<o></o>	8/2/2012<o></o>​	payments<o></o>
10<o></o>​	Cindy<o></o>	8/2/2012<o></o>​	modify<o></o>

<TBODY>
</TBODY>

<o> </o>
Notice that on any given day, an employee might do any number of tasks or none. But if they do none, then it should not be included in the calculation of their average because they didn't work that day. The dates are always in ascending order. In the above example, Cindy did only two tasks on 08-01-12 while Andy only did one. But Andy's 90 day average might be 2.89 tasks per day.
<o> </o>
Kind Thanks,
Eddie

 

[Smitty]

You might want to try a Pivot Table first, especially as your data is set up to support one.

 

[Aladin Akyurek]

We’re trying to calculate the average number of tasks per day for each employee. The goal is to measure productivity of employees. This needs to be measured everyday.
<o></o>
So our goal is to report it as follows (for example);
<o></o>
The average tasks per day over the last 14 days for Mike was 3.79.
Mikes average was 4.03 for the last 90 days. But this must only take into account the day on which they actually completed at least one task so that days with zero tasks are not measured into their average because they didn't work that day.
<o></o>
The data is as follows (not actual data, example only);

<o></o>​	A<o></o>​	B<o></o>​	C<o></o>​
1<o></o>​	Name<o></o>	Date<o></o>	Task<o></o>
2<o></o>​	Mike<o></o>	8/1/2012<o></o>​	billing<o></o>
3<o></o>​	Cindy<o></o>	8/1/2012<o></o>​	payments<o></o>
4<o></o>​	Andy<o></o>	8/1/2012<o></o>​	billing<o></o>
5<o></o>​	Mike<o></o>	8/1/2012<o></o>​	billing<o></o>
6<o></o>​	Mike<o></o>	8/1/2012<o></o>​	modify<o></o>
7<o></o>​	Cindy<o></o>	8/1/2012<o></o>​	modify<o></o>
8<o></o>​	Cindy<o></o>	8/2/2012<o></o>​	payments<o></o>
9<o></o>​	Andy<o></o>	8/2/2012<o></o>​	payments<o></o>
10<o></o>​	Cindy<o></o>	8/2/2012<o></o>​	modify<o></o>

<tbody>
</tbody>

<o></o>
Notice that on any given day, an employee might do any number of tasks or none. But if they do none, then it should not be included in the calculation of their average because they didn't work that day. The dates are always in ascending order. In the above example, Cindy did only two tasks on 08-01-12 while Andy only did one. But Andy's 90 day average might be 2.89 tasks per day.
<o></o>
Kind Thanks,
Eddie

Not entirely clear. I could calculate an average of 0.14 for Mike based on the last 14 workdays (not 14 days) using the above sample. Can you verify the figure?

 

[eddiegnz1]

the table in my post is not actual data. it's just an example of how it's formated. The actual data will result in an average of 3 to 5 tasks per day per employee. The actual data has seven thousand rows and each date has about 50 tasks per date from all the employees combined.

 

[Aladin Akyurek]

the table in my post is not actual data. it's just an example of how it's formated. The actual data will result in an average of 3 to 5 tasks per day per employee. The actual data has seven thousand rows and each date has about 50 tasks per date from all the employees combined.

Try to verify the posted Mike figure though for the sample you provided and the last 14 workdays (without holidays if any).

 

[eddiegnz1]

I'm looking to achieve the following logic;
* out of the last 14 unique dates in the table, how many unique dates did mikes name occur
* in the last 14 dates in the table, how many tasks have mikes name next to it
* then divide the number of mikes tasks by the number of unique dates

 

[Aladin Akyurek]

Cindy's average is 2.00 over the last two days. The sample data I provided only has two dates so the 14 day average can not be calculated from the sample data I provided. You'd have to imagine a table that has thousands of dates on it and each and every weekday is included in the actual data.

I was asking for Mike...

So I'm looking to achieve the following logic;
* in the last 14 days, how many dates did mikes name occur
* in the last 14 days, how many tasks have mikes name next to it
* then divide the number of mikes tasks by the number of dates

That's exactly the logic I followed. The difference: I use 14 working days, not 14 days. Any comment on the latter point?

 

[eddiegnz1]

it is not based on working days rather it's based on the dates that appear on the table. In other words, it's based on the last 14 unique dates that appear on the table.

I'm looking to achieve the following logic;
* out of the last 14 unique dates in the table, how many unique dates did mikes name occur
* in the last 14 dates in the table, how many tasks have mikes name next to it
* then divide the number of mikes tasks by the number of unique dates

 

[Aladin Akyurek]

it is not based on working days rather it's based on the dates that appear on the table. In other words, it's based on the last 14 unique dates that appear on the table.

I'm looking to achieve the following logic;
* out of the last 14 unique dates in the table, how many unique dates did mikes name occur
* in the last 14 dates in the table, how many tasks have mikes name next to it
* then divide the number of mikes tasks by the number of unique dates

Precision is essential...

Average needs a denominator. Possibilities are:

a) last 14 days, this will include weekends and possibly holidays;
b) last 14 workdays, this excludes weekends and possibly holidays;
c) last 14 unique days the data table records.

You want the (c) option, one that requires a bit more processing. What follows is the sample you poste with an additional record for Cindy

			0			14
Name	Date	Task	Distinct Date		Name	Avg
Mike	8/1/2012	billing	1		Mike	0.142857143
Cindy	8/1/2012	payments	1		Cindy	0.214285714
Andy	8/1/2012	billing	1		Andy	0.142857143
Mike	8/1/2012	billing	1
Mike	8/1/2012	modify	1
Cindy	8/1/2012	modify	1
Cindy	8/2/2012	payments	2
Cindy	8/2/2012	savings	2
Andy	8/2/2012	payments	2
Cindy	8/2/2012	modify	2

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 63pt; mso-width-source: userset; mso-width-alt: 2986;" width="84"> <col style="width: 96pt; mso-width-source: userset; mso-width-alt: 4551;" width="128"> <col style="width: 103pt; mso-width-source: userset; mso-width-alt: 4864;" width="137"> <col style="width: 48pt;" width="64"> <col style="width: 53pt; mso-width-source: userset; mso-width-alt: 2503;" width="70"> <col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3128;" width="88"> <tbody>
</tbody>

A2:C12 houses the sample, the headers included.

D1 must house a 0.

D3, just enter and copy down:

=IF(ISNUMBER(MATCH($B3,$B$2:B2,0)),VLOOKUP($B3,$B2:$D2,3,0),LOOKUP(9.99E+307,$D$1:$D2)+1)

The foregoing tags the unique dates.

The employee names are located in the range in F.

G1: 14 (a size, i.e., a window for the last unique days)

G3, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($C$3:$C$12<>"",IF($A$3:$A$12=$F3,IF($D$3:$D$12<=$G$1,
  MATCH($C$3:$C$12,$C$3:$C$12,0)))),ROW($A$3:$A$12)-ROW($A$3)+1),1))/$G$1

 

[eddiegnz1]

Aladin, would you please accept my apology for not being clear. I am not very good at putting this stuff into words. But now I know that you’re clear that it is possibility C from your list of three.
<o></o>
But there’s still a bit more that I must try to clarify. For Cindy, in the above sample, her average will be 2.50 not 0.214285. That’s because she completed 5 tasks in two days. So 5 divided by 2 equals 2.5.
<o></o>
The actual data set will definitely have more than 14 unique dates. It will have hundreds of unique dates.
<o></o>
So for the last 14 unique dates in which Cindy completed at least one task, she might have completed 30 tasks. So 30 divided by 14 would be 2.14.

Kind thanks,
Eddie