Formula Help: Sharing a bonus payment across multiple employees

[voyto]

Hello All

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

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??

 

[MickG]

Try:-

(1+0+0.5+1+1) =3.5
1000/3.5 =285.7143
Emp 1	1	=B4*285.7143	285.7143
Emp 2	0	=B5*285.7143	0
Emp 3	0.5	=B6*285.7143	142.8572
Emp 4	1	=B7*285.7143	285.7143
Emp 5	1	=B8*285.7143	285.7143
		Total	1000

<tbody>
</tbody>

 

[Fluff]

How about

Excel 2013/2016
	B	C	D	E
1	Pot	1000
2
3	E1	40	1	333.3333
4	E2	40	0	0
5	E3	40	0.5	166.6667
6	E4	20	1	166.6667
7	E5	40	1	333.3333
Update f

Cell Formulas
Range	Formula
E3	=C3*D3*(C$1/SUMPRODUCT(C$3:C$7*D$3:D$7))

 

[MickG]

Oops, looks like I missed the Hours !!!!

 

[voyto]

You clever sausage, you nailed it! This seems to have done it perfectly!

Can't thank you enough

 

[Fluff]

Glad we could help & thanks for the feedback