Formula Help: Sharing a bonus payment across multiple employees

voyto

New Member
Joined
Sep 30, 2018
Messages
7
Hello All :biggrin:

I have a spreadsheet I use to pay our employees on a 4-weekly basis. We also include a production bonus, which we're modifying to a performance based one. My head is a little pickled!...

I have a cell with a "Bonus Pot" for the month - this is the ££ figure I have to share across everyone.
I have a list of employees with a performance rating for the week, represented with a value (these need to be reviewed, but are currently things like 0.5, 1, 1.1, ....)
I have a cell with the bonus value per hour worked (the full weekly pot divided up by the total hours worked of all employees)

Let's give a quick scenario....

Code:
Weekly Pot Value = £1,000.00

Employee 1 - Worked 40 hrs - Performance Value 1
Employee 2 - Worked 40 hrs - Performance Value 0
Employee 3 - Worked 40 hrs - Performance Value 0.5
Employee 4 - Worked 20 hrs - Performance Value 1
Employee 5 - Worked 40 hrs - Performance Value 1

Employee 1 worked a full week.
Employee 2 earns nil bonus for the week.
Employee 3 was only rated 0.5 for their performance. Their bonus figure should reflect this.
Employee 4 worked only half a week, so their bonus value should reflect that.
Employee 5 worked a full week.

The total bonus payed out should add up to the weekly pot value. I can't seem to work out how to calculate this - am I being stupid?? :laugh:
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Try:-
[TABLE="width: 275"]
<tbody>[TR]
[TD="width: 122, bgcolor: transparent"](1+0+0.5+1+1) =3.5
[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 118, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1000/3.5 =285.7143

[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Emp 1
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]=B4*285.7143
[/TD]
[TD="bgcolor: transparent, align: right"]285.7143
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Emp 2
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent"]=B5*285.7143
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Emp 3
[/TD]
[TD="bgcolor: transparent, align: right"]0.5
[/TD]
[TD="bgcolor: transparent"]=B6*285.7143
[/TD]
[TD="bgcolor: transparent, align: right"]142.8572
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Emp 4
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]=B7*285.7143
[/TD]
[TD="bgcolor: transparent, align: right"]285.7143
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Emp 5
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="bgcolor: transparent"]=B8*285.7143
[/TD]
[TD="bgcolor: transparent, align: right"]285.7143
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Total
[/TD]
[TD="bgcolor: transparent, align: right"]1000
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
How about


Excel 2013/2016
BCDE
1Pot1000
2
3E1401333.3333
4E24000
5E3400.5166.6667
6E4201166.6667
7E5401333.3333
Update f
Cell Formulas
RangeFormula
E3=C3*D3*(C$1/SUMPRODUCT(C$3:C$7*D$3:D$7))
 
Upvote 0
You clever sausage, you nailed it! This seems to have done it perfectly!

Can't thank you enough :biggrin:
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,215
Members
452,618
Latest member
Tam84

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