Calculate Average Number of Tasks Per day, productivity

E

eddiegnz1

New Member
Joined
Jun 5, 2012
Messages
40
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:p> </o:p>
So our goal is to report it as follows (for example);
<o:p> </o:p>
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:p> </o:p>
The data is as follows (not actual data, example only);
<o:p> </o:p>​
A<o:p></o:p>​
B<o:p></o:p>​
C<o:p></o:p>​
1<o:p></o:p>​
Name<o:p></o:p>
Date<o:p></o:p>
Task<o:p></o:p>
2<o:p></o:p>​
Mike<o:p></o:p>
8/1/2012<o:p></o:p>​
billing<o:p></o:p>
3<o:p></o:p>​
Cindy<o:p></o:p>
8/1/2012<o:p></o:p>​
payments<o:p></o:p>
4<o:p></o:p>​
Andy<o:p></o:p>
8/1/2012<o:p></o:p>​
billing<o:p></o:p>
5<o:p></o:p>​
Mike<o:p></o:p>
8/1/2012<o:p></o:p>​
billing<o:p></o:p>
6<o:p></o:p>​
Mike<o:p></o:p>
8/1/2012<o:p></o:p>​
modify<o:p></o:p>
7<o:p></o:p>​
Cindy<o:p></o:p>
8/1/2012<o:p></o:p>​
modify<o:p></o:p>
8<o:p></o:p>​
Cindy<o:p></o:p>
8/2/2012<o:p></o:p>​
payments<o:p></o:p>
9<o:p></o:p>​
Andy<o:p></o:p>
8/2/2012<o:p></o:p>​
payments<o:p></o:p>
10<o:p></o:p>​
Cindy<o:p></o:p>
8/2/2012<o:p></o:p>​
modify<o:p></o:p>

<TBODY>
</TBODY>
<o:p> </o:p>
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:p> </o:p>
Kind Thanks,
Eddie
 
Sort by date Sort by votes
eddiegnz1 said:
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:p></o:p>
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:p></o:p>
The actual data set will definitely have more than 14 unique dates. It will have hundreds of unique dates.
<o:p></o:p>
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
Click to expand...

Is it:

Number of Cindy days (tasks, duplicate or not) divided by the number of unique dates Cindy was tasking?

This would mean a different denominator (unique days) for each employee...
 
Upvote 0

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Aladin Akyurek said:
Is it:

Number of Cindy days (tasks, duplicate or not) divided by the number of unique dates Cindy was tasking?

This would mean a different denominator (unique days) for each employee...
Click to expand...

eddiegnz1 said:
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:p></o:p>
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:p></o:p>
The actual data set will definitely have more than 14 unique dates. It will have hundreds of unique dates.
<o:p></o:p>
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
Click to expand...

Perhaps...
2
NameDateTask NameAvg
Mike8/1/2012billing Mike1.5
Cindy8/1/2012payments Cindy2.5
Andy8/1/2012billing Andy1
Mike8/1/2012billing
Mike8/1/2012modify
Cindy8/1/2012modify
Cindy8/2/2012payments
Cindy8/2/2012savings
Andy8/2/2012payments
Cindy8/2/2012modify

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 130pt; mso-width-source: userset; mso-width-alt: 6144;" width="173"> <col style="width: 114pt; mso-width-source: userset; mso-width-alt: 5404;" width="152"> <col style="width: 48pt;" span="4" width="64"> <tbody>
</tbody>


G1, control+shift+enter:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF($C$3:$C$12<>"",MATCH($B$3:$B$12,$B$3:$B$12,0)),
  ROW($B$3:$B$12)-ROW($B$3)+1),1))
This eestablishes the number of unique days in the data.

G3, just enter and copy down:
Rich (BB code): 
=IF($F3="","",COUNTIF($A$3:$A$12,$A3)/$G$1)
 
Upvote 0
Aha, Aladin, this works and is only missing one thing, the denominator should not be the same for every employee because sometimes employees are not present on all of the days in the data set. So the denominator should only include the number of days on which the employee completed at least one task.

An employee might have been on vacation for some of those days and thus completed no tasks that day. So we don't want to hurt their average by having a larger denominator in calculating their average.

I hope I don't wear out my welcome for your help. But this is so close, i think one more step will do.
Kind Thanks
Eddie
 
Upvote 0
eddiegnz1 said:
Aha, Aladin, this works and is only missing one thing, the denominator should not be the same for every employee because sometimes employees are not present on all of the days in the data set. So the denominator should only include the number of days on which the employee completed at least one task.

An employee might have been on vacation for some of those days and thus completed no tasks that day. So we don't want to hurt their average by having a larger denominator in calculating their average.

I hope I don't wear out my welcome for your help. But this is so close, i think one more step will do.
Kind Thanks
Eddie
Click to expand...

Eddie: Ok, but I'm worried about this productivity score!...

NameDateTask NameUnique Date CountAvg
Mike8/1/2012billing Mike13
Cindy8/1/2012payments Cindy22.5
Andy8/1/2012billing Andy21
Mike8/1/2012billing
Mike8/1/2012modify
Cindy8/1/2012modify
Cindy8/2/2012payments
Cindy8/2/2012savings
Andy8/2/2012payments
Cindy8/2/2012modify

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 55pt; mso-width-source: userset; mso-width-alt: 2588;" width="73"> <col style="width: 68pt; mso-width-source: userset; mso-width-alt: 3242;" width="91"> <col style="width: 48pt;" span="3" width="64"> <col style="width: 131pt; mso-width-source: userset; mso-width-alt: 6200;" width="174"> <col style="width: 48pt; mso-width-source: userset; mso-width-alt: 2275;" width="64"> <tbody>
</tbody>


G2, control+shift+enter and copy down:
Rich (BB code): 
=SUM(IF(FREQUENCY(IF($C$2:$C$11<>"",
  IF($A$2:$A$11=$F2,MATCH($B$2:$B$11,$B$2:$B$11,0))),
   ROW($B$2:$B$11)-ROW($B$2)+1),1))
H2, just enter and copy down:
Rich (BB code): 
=IF($F2="","",COUNTIF($A$2:$A$11,$A2)/$G2)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Calculate total pay for employee who may be assigned multiple shifts/tasks in one calendar day (data in 2 different tables) - is IF my only option?
Replies
2
Views
328
JamesCanale
JamesCanale
D
  • Question
calculating billable hours by week number
Replies
1
Views
543
sijpie
sijpie
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P
K
  • Solved
Productivity Formula
Replies
3
Views
901
ExceLoki
ExceLoki
A
  • Solved
Calendar form cancel button passing data to worksheet
Replies
7
Views
720
AndyEd
A

Forum statistics

Threads
1,218,252
Messages
6,141,396
Members
450,355
Latest member
twmills

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top