Redistribute remaining bonus after a reaching a cap

[Mtnfan]

I need to distribute a quarterly bonus based on production.
A bonus pool is created by adding a certain dollar amount for every hour worked. In the case of this example, $12 is added to the pool for every hour worked.
The bonus pool is then distributed based on each worker's relative production in a given quarter.

1. We first calculate each worker's total widgets produced by multiplying widgets/hour x hours worked in a given quarter. For the bonus calculation, only widgets/hour over a certain threshold are counted. In this example, the threshold is 2 widgets/hour.
2. Each worker then receives a bonus proportional to the number of "bonus" widgets they produced. For example, if Bob produced 2 widgets beyond the threshold and the total pool of "bonus" widgets is 10, Bob receives 20% of the bonus pool.
3. The complicating step is there is a cap on the bonus rate. In this case the cap is $15/hour. The primary goal of the cap is to avoid an excessive focus on production and to keep the bonus distribution reasonable between workers. However, applying the cap creates a pool of undistributed bonus dollars remaining. I want to distribute all the dollars in the bonus pool by redistributing the remaining bonus to the uncapped workers. This may take more than one iteration.

I could manually recalculate the distribution for as many iterations as needed until the bonus pool is completely distributed. However, what I would like to do is to automatically redistribute the remaining bonus pool to the uncapped workers. Ideally, the formula would consider the relative contributions of each remaining worker (since each worker's relative contribution changes once a "capped" worker is removed from the pool).

Book3
	A	B	C	D	E	F	G	H
1	Q4 bonus distribution
2	Bonus pool	$ 27,300.00			Widgets/Hour Threshold
3					2
4	Name	Production (widgets/hour)	Hours worked	Total Widgets Produced	Adjusted Widgets Produced for bonus	Bonus	Bonus per hour	$15 Capped Bonus per hour
5	Bob	5	500	2500	1500	$ 4,636.94	$ 9.27	$ 9.27
6	Mary	4.75	475	2256.25	1306.25	$ 4,038.00	$ 8.50	$ 8.50
7	Sally	6.5	450	2925	2025	$ 6,259.87	$ 13.91	$ 13.91
8	John	5.25	400	2100	1300	$ 4,018.68	$ 10.05	$ 10.05
9	Linda	8	450	3600	2700	$ 8,346.50	$ 18.55	$ 15.00
10
11						Undistributed bonus		$ 1,596.50
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUM(C5:C9)*12
D5:D9	D5	=B5*C5
E5:E9	E5	=(B5-$E$3)*C5
F5:F9	F5	=(E5/SUM($E$5:$E$9))*$B$2
G5:G9	G5	=F5/C5
H5:H9	H5	=IF((((E5/SUM($E$5:$E$9))*$B$2)/C5)>15,15,(((E5/SUM($E$5:$E$9))*$B$2)/C5))
H11	H11	=B2-SUMPRODUCT(H5:H9,C5:C9)

 

[Dave Patton]

Thanks for the feedback.