Calculating Productivity

P

ProAssist

New Member
Joined
Feb 25, 2016
Messages
5
It has been about 20 years since I had to make a spreadsheet for work.
I am so rusty! :confused:
The director wants to calculate productivity of our case workers, in order to award bonuses.

Caseworkers can bill 5hrs per 8 hr day.
Sometimes they will bill under 5. We multiply the amounts under 5 by .625

So far...
I have calculated how many workable days in a month(-observed holidays)
-grouped those into quarters(dec-feb, etc)
-added expected hrs per month (workable days x5)
-added expected hrs per quarter (I added those below the chart)
-made a column that multiplies amounts under 5 x.625
-made a column that calculates productivity by percent

Now I just need to figure out how to create formulas that will:
-multiply amounts under 5 x.625 but also calculate hours over 5 correctly because if a caseworker takes 8 hours of vacation time (PPL) I get a "false". Even with a hidden column, the math isn't working out.

< [TABLE="width: 591"]
<colgroup><col style="width:76.50pf;" width="102"> <col style="width:53.25pf;" width="71"> <col style="width:54.75pf;" width="73"> <col style="width:47.25pf;" width="63"> <col style="width:41.25pf;" width="55"> <col style="width:46.50pf;" width="62"> <col style="width:48.00pf;" width="64"> <col style="width:47.25pf;" width="63"> <col style="width:46.50pf;" width="62"> <col style="width:62.25pf;" width="83"> <col style="width:68.25pf;" width="91"> </colgroup><tbody>[TR]
[TD="class: et8, width: 102, align: center"]Month 2016[/TD]
[TD="class: et9, width: 71, align: center"]Week Days[/TD]
[TD="class: et10, width: 73, align: center"]Holidays[/TD]
[TD="class: et9, width: 63, align: center"]Days Worked[/TD]
[TD="class: et12, width: 55, align: center"]x 5 Hrs[/TD]
[TD="class: et13, width: 62, align: center"]Expected Hours[/TD]
[TD="class: et14, width: 64, align: center"]PPL Hours[/TD]
[TD="class: et15, width: 63, align: center"][/TD]
[TD="class: et44, width: 62, align: center"][/TD]
[TD="class: et45, width: 83, align: center"]Productivity[/TD]
[TD="class: et46, width: 91, align: center"]Timeliness[/TD]
[/TR]
[TR]
[TD="class: et16, width: 102, align: general"]December[/TD]
[TD="class: et17, width: 71, align: center"]22[/TD]
[TD="class: et18, width: 73, align: center"]1[/TD]
[TD="class: et18, width: 63, align: center"]21[/TD]
[TD="class: et20, width: 55, align: center"]5[/TD]
[TD="class: et20, width: 62, align: center"]105[/TD]
[TD="class: et18, width: 64, align: center"]2[/TD]
[TD="class: et21, width: 63, align: center"]1.25[/TD]
[TD="class: et47, width: 62, align: center"]103.75[/TD]
[TD="class: et48, width: 83, align: center"]99%[/TD]
[TD="class: et49, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et22, width: 102, align: general"]January[/TD]
[TD="class: et23, width: 71, align: center"]20[/TD]
[TD="class: et24, width: 73, align: center"]2[/TD]
[TD="class: et18, width: 63, align: center"]18[/TD]
[TD="class: et25, width: 55, align: center"]5[/TD]
[TD="class: et20, width: 62, align: center"]90[/TD]
[TD="class: et26, width: 64, align: center"]8[/TD]
[TD="class: et21, width: 63, align: center"]FALSE[/TD]
[TD="class: et47, width: 62, align: center"]90[/TD]
[TD="class: et48, width: 83, align: center"]100%[/TD]
[TD="class: et50, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et22, width: 102, align: general"]February[/TD]
[TD="class: et23, width: 71, align: center"]21[/TD]
[TD="class: et24, width: 73, align: center"]1[/TD]
[TD="class: et18, width: 63, align: center"]20[/TD]
[TD="class: et25, width: 55, align: center"]5[/TD]
[TD="class: et20, width: 62, align: center"]100[/TD]
[TD="class: et26, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"]0.00[/TD]
[TD="class: et47, width: 62, align: center"]100[/TD]
[TD="class: et48, width: 83, align: center"]100%[/TD]
[TD="class: et50, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et22, width: 102, align: general"]March[/TD]
[TD="class: et23, width: 71, align: center"]23[/TD]
[TD="class: et24, width: 73, align: center"]0[/TD]
[TD="class: et18, width: 63, align: center"]23[/TD]
[TD="class: et25, width: 55, align: center"]5[/TD]
[TD="class: et20, width: 62, align: center"]115[/TD]
[TD="class: et26, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"]0.00[/TD]
[TD="class: et47, width: 62, align: center"]115[/TD]
[TD="class: et48, width: 83, align: center"]100%[/TD]
[TD="class: et50, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et22, width: 102, align: general"]April[/TD]
[TD="class: et23, width: 71, align: center"]21[/TD]
[TD="class: et24, width: 73, align: center"]0[/TD]
[TD="class: et18, width: 63, align: center"]21[/TD]
[TD="class: et25, width: 55, align: center"]5[/TD]
[TD="class: et20, width: 62, align: center"]105[/TD]
[TD="class: et26, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"]0.00[/TD]
[TD="class: et47, width: 62, align: center"]105[/TD]
[TD="class: et48, width: 83, align: center"]100%[/TD]
[TD="class: et50, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et22, width: 102, align: general"]May[/TD]
[TD="class: et23, width: 71, align: center"]22[/TD]
[TD="class: et24, width: 73, align: center"]1[/TD]
[TD="class: et18, width: 63, align: center"]21[/TD]
[TD="class: et25, width: 55, align: center"]5[/TD]
[TD="class: et20, width: 62, align: center"]105[/TD]
[TD="class: et26, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"]0.00[/TD]
[TD="class: et47, width: 62, align: center"]105[/TD]
[TD="class: et48, width: 83, align: center"]100%[/TD]
[TD="class: et50, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et22, width: 102, align: general"]June[/TD]
[TD="class: et23, width: 71, align: center"]22[/TD]
[TD="class: et24, width: 73, align: center"]0[/TD]
[TD="class: et18, width: 63, align: center"]22[/TD]
[TD="class: et25, width: 55, align: center"]5[/TD]
[TD="class: et20, width: 62, align: center"]110[/TD]
[TD="class: et26, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"]0.00[/TD]
[TD="class: et47, width: 62, align: center"]110[/TD]
[TD="class: et48, width: 83, align: center"]100%[/TD]
[TD="class: et50, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et22, width: 102, align: general"]July[/TD]
[TD="class: et23, width: 71, align: center"]20[/TD]
[TD="class: et24, width: 73, align: center"]1[/TD]
[TD="class: et18, width: 63, align: center"]19[/TD]
[TD="class: et25, width: 55, align: center"]5[/TD]
[TD="class: et20, width: 62, align: center"]95[/TD]
[TD="class: et26, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"]0.00[/TD]
[TD="class: et47, width: 62, align: center"]95[/TD]
[TD="class: et48, width: 83, align: center"]100%[/TD]
[TD="class: et50, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et22, width: 102, align: general"]August[/TD]
[TD="class: et23, width: 71, align: center"]23[/TD]
[TD="class: et24, width: 73, align: center"]0[/TD]
[TD="class: et18, width: 63, align: center"]23[/TD]
[TD="class: et25, width: 55, align: center"]5[/TD]
[TD="class: et20, width: 62, align: center"]115[/TD]
[TD="class: et26, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"]0.00[/TD]
[TD="class: et47, width: 62, align: center"]115[/TD]
[TD="class: et48, width: 83, align: center"]100%[/TD]
[TD="class: et50, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et22, width: 102, align: general"]September[/TD]
[TD="class: et23, width: 71, align: center"]22[/TD]
[TD="class: et24, width: 73, align: center"]1[/TD]
[TD="class: et18, width: 63, align: center"]21[/TD]
[TD="class: et25, width: 55, align: center"]5[/TD]
[TD="class: et20, width: 62, align: center"]105[/TD]
[TD="class: et26, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"]0.00[/TD]
[TD="class: et47, width: 62, align: center"]105[/TD]
[TD="class: et48, width: 83, align: center"]100%[/TD]
[TD="class: et50, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et22, width: 102, align: general"]October[/TD]
[TD="class: et23, width: 71, align: center"]21[/TD]
[TD="class: et24, width: 73, align: center"]0[/TD]
[TD="class: et18, width: 63, align: center"]21[/TD]
[TD="class: et25, width: 55, align: center"]5[/TD]
[TD="class: et20, width: 62, align: center"]105[/TD]
[TD="class: et26, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"]0.00[/TD]
[TD="class: et47, width: 62, align: center"]105[/TD]
[TD="class: et48, width: 83, align: center"]100%[/TD]
[TD="class: et51, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et22, width: 102, align: general"]November[/TD]
[TD="class: et27, width: 71, align: center"]22[/TD]
[TD="class: et8, width: 73, align: center"]2[/TD]
[TD="class: et18, width: 63, align: center"]20[/TD]
[TD="class: et29, width: 55, align: center"]5[/TD]
[TD="class: et20, width: 62, align: center"]100[/TD]
[TD="class: et26, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"]0.00[/TD]
[TD="class: et47, width: 62, align: center"]100[/TD]
[TD="class: et48, width: 83, align: center"]100%[/TD]
[TD="class: et52, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et30, width: 102, align: general"][/TD]
[TD="class: et31, width: 71, align: center"]259[/TD]
[TD="class: et32, width: 73, align: center"]9[/TD]
[TD="class: et32, width: 63, align: center"]250[/TD]
[TD="class: et34, width: 55, align: center"]60[/TD]
[TD="class: et34, width: 62, align: center"]1250[/TD]
[TD="class: et35, width: 64, align: center"][/TD]
[TD="class: et36, width: 63, align: center"][/TD]
[TD="class: et53, width: 62, align: center"][/TD]
[TD="class: et54, width: 83, align: center"][/TD]
[TD="class: et55, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et37, width: 102, align: general"][/TD]
[TD="class: et38, width: 71, align: center"][/TD]
[TD="class: et38, width: 73, align: center"][/TD]
[TD="class: et39, width: 63, align: center"][/TD]
[TD="class: et39, width: 55, align: center"][/TD]
[TD="class: et39, width: 62, align: center"][/TD]
[TD="class: et39, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"][/TD]
[TD="class: et39, width: 62, align: center"][/TD]
[TD="class: et56, width: 83, align: center"][/TD]
[TD="class: et57, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et37, width: 102, align: general"][/TD]
[TD="class: et40, width: 71, align: center"]Expected Hours[/TD]
[TD="class: et40, width: 73, align: center"]Billed Hours[/TD]
[TD="class: et39, width: 63, align: center"]Bonus[/TD]
[TD="class: et39, width: 55, align: center"][/TD]
[TD="class: et39, width: 62, align: center"][/TD]
[TD="class: et39, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"][/TD]
[TD="class: et39, width: 62, align: center"][/TD]
[TD="class: et56, width: 83, align: center"][/TD]
[TD="class: et57, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et42, width: 102, align: general"]Q1[/TD]
[TD="class: et38, width: 71, align: center"]295[/TD]
[TD="class: et38, width: 73, align: center"]293.75[/TD]
[TD="class: et39, width: 63, align: center"][/TD]
[TD="class: et39, width: 55, align: center"][/TD]
[TD="class: et39, width: 62, align: center"][/TD]
[TD="class: et39, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"][/TD]
[TD="class: et39, width: 62, align: center"][/TD]
[TD="class: et56, width: 83, align: center"][/TD]
[TD="class: et57, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et42, width: 102, align: general"]Q2[/TD]
[TD="class: et38, width: 71, align: center"]325[/TD]
[TD="class: et38, width: 73, align: center"]325[/TD]
[TD="class: et39, width: 63, align: center"][/TD]
[TD="class: et39, width: 55, align: center"][/TD]
[TD="class: et39, width: 62, align: center"][/TD]
[TD="class: et39, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"][/TD]
[TD="class: et39, width: 62, align: center"][/TD]
[TD="class: et56, width: 83, align: center"][/TD]
[TD="class: et57, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et42, width: 102, align: general"]Q3[/TD]
[TD="class: et38, width: 71, align: center"]320[/TD]
[TD="class: et38, width: 73, align: center"]320[/TD]
[TD="class: et39, width: 63, align: center"][/TD]
[TD="class: et39, width: 55, align: center"][/TD]
[TD="class: et39, width: 62, align: center"][/TD]
[TD="class: et39, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"][/TD]
[TD="class: et39, width: 62, align: center"][/TD]
[TD="class: et56, width: 83, align: center"][/TD]
[TD="class: et57, width: 91, align: center"][/TD]
[/TR]
[TR]
[TD="class: et42, width: 102, align: general"]Q4[/TD]
[TD="class: et38, width: 71, align: center"]310[/TD]
[TD="class: et38, width: 73, align: center"]310[/TD]
[TD="class: et39, width: 63, align: center"][/TD]
[TD="class: et39, width: 55, align: center"][/TD]
[TD="class: et39, width: 62, align: center"][/TD]
[TD="class: et39, width: 64, align: center"][/TD]
[TD="class: et21, width: 63, align: center"][/TD]
[TD="class: et39, width: 62, align: center"][/TD]
[TD="class: et56, width: 83, align: center"][/TD]
[TD="class: et57, width: 91, align: center"][/TD]
[/TR]
</tbody>[/TABLE]


I used to be so good at formulas. Now I can't wrap my head around them. :eeek:
Help! ...if you can.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Ok,

I think I know what you are going for.

Assuming PPL is the amount of hours they worked and it is in column G.

=IF(G2<5,G2*0.625,5)

This says if the value in G2 is less than 5, multiply that value by 0.625, anything else the value will be 5.
 
Last edited:
Upvote 0
This does help. Thank you for taking the time!
However, PPL is the 'paid personal leave' (time/hours taken off per month)
THIS is my problem column because they can take more than 5 hours off. What happens if the hours are over 5 and how to calculate is tripping me up!
 
Upvote 0
So what do you want the output to be? I assume 1 will be 0.625, 2 will be 1.25, etc. What do you want 5-8 to be?
 
Upvote 0
Anything over 5 hours does not have to be prorated.

Here is where I have hit a road block because if a caseworker takes off 2 days that = 10 hours of PPL. Easy.
But how do I catch it if someone takes 7 hours or 13?!
My brain is tired.
 
Upvote 0
Are you familiar with conditional formatting? It can flag any values that go over 5. Try a google search of it, it isn't overly complex.
 
Upvote 0
I think I know what you need now...

=IF(G2<5,G2*0.625,0.625*5)
 
Upvote 0
Hi,

In order to possibly help, please show what formulas you're currently using and the expected results (especially H3:I5).


Excel 2010
ABCDEFGHIJK
1Month 2016Week DaysHolidaysDays Workedx 5 HrsExpected HoursPPL HoursProductivityTimeliness
2December22121510521.25103.7599%
3January202185908FALSE90100%
4February211205100100100100%
5March230235115130115100%
6April2102151050105100%
7May2212151050105100%
8June2202251100110100%
9July20119595095100%
10August2302351150115100%
11September2212151050105100%
12October2102151050105100%
13November2222051000100100%
142599250601250
15
16Expected HoursBilled HoursBonus
17Q1295293.75
18Q2325325
19Q3320320
20Q4310310
Sheet1
 
Last edited:
Upvote 0
Fantastic!! Thank you for the refreshers!
I will continue to educate myself so that some day I may offer some help here!
This is so great!
Thank you.
I will come back if it isn't what administration wants - haha!
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Tracking individual codes by date and quantity
Replies
1
Views
37
Vorkosigan
V
M
  • Solved
Add item in first digits with create space after it based on sheet name
Replies
8
Views
111
kvsrinivasamurthy
K
A
  • Solved
Track movement stock in listbox on userform by copy amounts from previous row to next row
Replies
8
Views
93
abdo meghari
A
H
  • Question Question
Calculate stake based on cumulative bank
Replies
0
Views
83
honkin
H
A
  • Solved
Merge QTY & AMOUNT for each part of item for each range
Replies
2
Views
65
Amer Omar
A

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

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