Help with a formula

[JSDyck]

example #1

Payment per job	55
Employee	Hours	pay
Employee #1	1	22
Employee #2	1	20
Employee #3	1	13
Total hours	3

<tbody>
</tbody>

example #2

Payment per job	554
Employee	Hours	pay
Employee #1	9	198
Employee #2	10	200
Employee #3	12	156
Total hours	31

<tbody>
</tbody>

I am looking for some help with a formula that will calculate the pay for each employee based on the amount of hours that they work and their percentage. We pay our employees by the job, as seen in the examples. If they work the same amount of hours or get the same percentage per job I can figure this out but the trouble is when they work different hours. (Like employee #1 works 6 hours on the job and the other 2 work 11 hours) How can I keep the proportions or percentages the same?

In the 2 examples above I show what I would like it to look like for these amounts but if they did the job in less time or one employee works more hours the rates would change.

What I want to be able to do is enter the price per job and the hours that each employee works and then it will proportionally figure out their pay for that job.

Does that make sense? Thanks in advance.

(I am using Windows 7 and Excel 2007)

 

[Dryver14]

Sheet1

	A	B	C	D
1	Payment per job	554
2	Employee
3	Employee 1	9		160.84
4	Employee 2	10		178.71
5	Employee 3	12		214.45
6
7		31		554.00

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 123px;"><col style="width: 73px;"><col style="width: 64px;"><col style="width: 64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula D3 =($B$1/$B$7)*B3 D4 =($B$1/$B$7)*B4 D5 =($B$1/$B$7)*B5 B7 =SUM(B3:B5) D7 =SUM(D3:D5) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Wookiee]

I think I was able to set up some example formulas to determine the calculations you need. I had my calculation determine the total payment amount, the total hours worked, then derived the percentage of each worker's contribution. Finally, it multiplied the percentage by the total payment. I would like to double check with you about your example figures before I post my formulas, because what I came up with was slightly different than your example data.

In Example 1, my calculations determined that all 3 employees should be paid $18.33 apiece since they all worked one hour.

In Example 2, I came up with $160.84, $178.71, and $214.45 (representing 29%, 32%, and 39% of the worked hours respectively).

Is there some additional factor going on which I didn't pick up on?

 

[JSDyck]

Thank you for your response. The problem I have is when I do it this way and I change their hours so they are all the same then their pay is also the same but I need it 'proportionally' different like I showed in the first example.

 

[JSDyck]

regarding your question Wookiee, The employees do not get payed all the same even if they work the same hours because one is a beginner and the other 2 have more experience so the get a larger percentage with the largest percentage going to the lead hand. So each time the variables will change because their percentage changes when they work different hours. the first example shows what they would get payed if they each work one hour.

 

[Wookiee]

Yes, it's the proportional difference that I'm not fully understanding. Just to clarify, are you saying that you want Employee 1 to get paid more per hour than Employee 2, and Employee 2 gets more per hour than Employee 1? I think I can modify the calculations I came up with (and Dryver14 beat me to the punch posting), but I'd need to know specifically if that's the case.

If my previous hypothesis is correct, you're grading so that E1 gets 122% of the average hourly rate, E2 gets 111%, and E3 gets only 72.22%, right? (Poor Employee 3 must be the low man on the totem).

 

[Wookiee]

Man, it's taking me so long to post replies that I seem to miss one or two posts in the mean time. But I think I'm caught up now.

To use Dryver14's example, I would just add *1.2222 to the formula in D3, *1.1111 to the D4 formula, and *.7222 to D5. By using that calculation, I came up with payouts of $196.58, $198.56, and $154.88 for Example 2 in your scenario. Mind you, they're rounded up for the 2-digit currency format, but maybe you can give Employee 3 the leftover change to make him feel better about the gap in pay rates.

 

[shg]

There's a workbook with two examples of how to do this at https://www.box.com/s/7eure8q5bljsn9nkjgx2. It describes allocation of a tip-out, but I think the idea is the same.

 

[JSDyck]

This last one will work perfect for what I need. Thanks a lot for all your help.

 

[shg]

You're welcome.