Help with a formula

JSDyck

New Member
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)

Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
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>

 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

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?

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.

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.

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).

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.

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

You're welcome.

Replies
2
Views
168
Replies
1
Views
207
Replies
1
Views
178
Replies
1
Views
289
Replies
13
Views
450

1,203,525
Messages
6,055,916
Members
444,834
Latest member
ComputerExcel

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.

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

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