Calculating Productivity

[ProAssist]

It has been about 20 years since I had to make a spreadsheet for work.
I am so rusty!
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.

<

Month 2016	Week Days	Holidays	Days Worked	x 5 Hrs	Expected Hours	PPL Hours			Productivity	Timeliness
December	22	1	21	5	105	2	1.25	103.75	99%
January	20	2	18	5	90	8	FALSE	90	100%
February	21	1	20	5	100		0.00	100	100%
March	23	0	23	5	115		0.00	115	100%
April	21	0	21	5	105		0.00	105	100%
May	22	1	21	5	105		0.00	105	100%
June	22	0	22	5	110		0.00	110	100%
July	20	1	19	5	95		0.00	95	100%
August	23	0	23	5	115		0.00	115	100%
September	22	1	21	5	105		0.00	105	100%
October	21	0	21	5	105		0.00	105	100%
November	22	2	20	5	100		0.00	100	100%
	259	9	250	60	1250
	Expected Hours	Billed Hours	Bonus
Q1	295	293.75
Q2	325	325
Q3	320	320
Q4	310	310

<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>
</tbody>

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

 

[seguin85]

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.

 

[ProAssist]

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!

 

[seguin85]

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?

 

[ProAssist]

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.

 

[seguin85]

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.

 

[seguin85]

I think I know what you need now...

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

 

[jtakw]

Hi,

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

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	Month 2016	Week Days	Holidays	Days Worked	x 5 Hrs	Expected Hours	PPL Hours			Productivity	Timeliness
2	December	22	1	21	5	105	2	1.25	103.75	99%
3	January	20	2	18	5	90	8	FALSE	90	100%
4	February	21	1	20	5	100	10	0	100	100%
5	March	23	0	23	5	115	13	0	115	100%
6	April	21	0	21	5	105		0	105	100%
7	May	22	1	21	5	105		0	105	100%
8	June	22	0	22	5	110		0	110	100%
9	July	20	1	19	5	95		0	95	100%
10	August	23	0	23	5	115		0	115	100%
11	September	22	1	21	5	105		0	105	100%
12	October	21	0	21	5	105		0	105	100%
13	November	22	2	20	5	100		0	100	100%
14		259	9	250	60	1250
15
16		Expected Hours	Billed Hours	Bonus
17	Q1	295	293.75
18	Q2	325	325
19	Q3	320	320
20	Q4	310	310
Sheet1

 

[ProAssist]

Oh my gosh. Duh. Ok, Thank you!

 

[ProAssist]

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!