Formula to divide total amount according to percentage of a target

[ToddMBaker]

Hi! I am creating a spreadsheet to calculate the correct split of a bonus pool amongst three employees. Each employee has a current pay rate and a target pay rate. We want to split the bonus pool so that all employees end up with the same percentage of their target pay rate. I have the sheet working so long as the bonus pool is large enough to get all employees to an equal percent of target, but when the bonus pool is smaller, the employee with the lowest percent of target ends up getting a smaller amount. I would be happy to add more commentary, but I think it might be easier to just look at the sheet which I have tried to include using XL2BB. Please let me know if I need to provide more information.

In the example sheet, any bonus pool greater than or equal to 12,333.33 works correctly (i.e., each employee ends up with the same percent of their target/goal salary). Any pool size less than 12,333.33 result in Employee 1 getting less than they should.

Bonus Pool Calculation - example.xlsx
	A	B	C	D	E
1	Bonus Pool	$10,000.00
2
3	Employee	Employee 1	Employee 2	Employee 3	Q1 Totals
4	Amount paid	$65,000.00	$44,000.00	$56,000.00	$165,000.00
5	Quarterly Target Pay	$80,000.00	$50,000.00	$60,000.00	$190,000.00
6	% of Target	81.25%	88.00%	93.33%
7	Equilization amount	$9,666.67	$2,666.67	$0.00	$12,333.33
8	Split Remainder	$0.00	$0.00	$0.00	$0.00
9	Total bonus	$7,837.84	$2,162.16	$0.00	$10,000.00
10	New Quarterly comp	$72,837.84	$46,162.16	$56,000.00
11	New % of goal	91.05%	92.32%	93.33%
2021 Compensation

Cell Formulas
Range	Formula
E4:E5,E8:E9	E4	=SUM(B4:D4)
B6:D6	B6	=B4/B5
B7:D7	B7	=(MAX($B$6:$D$6)-B6)*B5
E7	E7	=SUM(B7:C7)
B8:D8	B8	=IF((MIN(B5-B4-B7,(B4+B7)/($E$4+$E$7)*($B$1-$E$7)))>0,MIN(B5-B4-B7,(B4+B7)/($E$4+$E$7)*($B$1-$E$7)),0)
B9:D9	B9	=IF($E$7<$B$1,(B7+B8),((B7/SUM($B$7:$D$7))*$B$1))
B10:D10	B10	=B4+B9
B11:D11	B11	=B10/B5

 

[Peter_SSs]

Welcome to the MrExcel board!

We want to split the bonus pool so that all employees end up with the same percentage of their target pay rate.

Unless you are going to take some money that has already been paid to an employee away from them, your goal is not possible in some circumstances.

With your example the total target pay is 190,000. The total amount to split is the total already paid plus the bonus (165,000 + 10,000 = 175,000)
175,000 is 92.11% of 190,000.

So, looking at Employee 3 their total equal share (92.11%) of the total amount available (175,000)

However, Employee 3 has already been paid 56,000/60,000 which, as your sheet shows, is 93.33%. That is, they have already been paid more than the 'equal percentage' of 92.11%

If you did want them all to receive the same % of goal, you would have to take $736.84 away from Employee 3 - see below.

If you don't want to do that then what do you want to happen?

ToddMBaker.xlsm
	A	B	C	D	E
1	Bonus Pool	10000
2
3	Employee	Employee 1	Employee 2	Employee 3	Q1 Totals
4	Amount paid	65,000.00	44,000.00	56,000.00	165,000.00
5	Quarterly Target Pay	80,000.00	50,000.00	60,000.00	190,000.00
6	% of Target	81.25%	88.00%	93.33%
7
8
9	Total bonus	8,684.21	2,052.63	-736.84	10,000.00
10	New Quarterly comp	73,684.21	46,052.63	55,263.16
11	New % of goal	92.11%	92.11%	92.11%
Sheet2

Cell Formulas
Range	Formula
E4:E5,E9	E4	=SUM(B4:D4)
B6:D6	B6	=B4/B5
B9:D9	B9	=($E$4+$B$1)/$E$5*B5-B4
B10:D10	B10	=B4+B9
B11:D11	B11	=B10/B5

 

[Peter_SSs]

If you don't want to do that then what do you want to happen?

Perhaps something like this?

ToddMBaker.xlsm
	A	B	C	D	E	F	G
1	Bonus Pool	10,000
2
3	Employee	Employee 1	Employee 2	Employee 3	Q1 Totals	Q1+Bonus	Q1+Bonus (Relevant Employees)
4	Amount paid	65,000.00	44,000.00	56,000.00	165,000.00	175,000.00	119,000.00
5	Quarterly Target Pay	80,000.00	50,000.00	60,000.00	190,000.00		130,000.00
6	% of Target	81.25%	88.00%	93.33%		92.11%	91.54%
7
8
9	Total bonus	8,230.77	1,769.23	0.00	10,000.00
10	New Quarterly comp	73,230.77	45,769.23	56,000.00
11	New % of goal	91.54%	91.54%	93.33%
Sheet3

Cell Formulas
Range	Formula
F4	F4	=E4+B1
E4:E5,E9	E4	=SUM(B4:D4)
G4	G4	=SUMIF(B6:D6,"<"&F6,B4:D4)+B1
G5	G5	=SUMIF(B6:D6,"<"&F6,B5:D5)
B6:D6,G6	B6	=B4/B5
F6	F6	=F4/E5
B9:D9	B9	=IF(B6<$F$6,$G$6*B5-B4,0)
B10:D10	B10	=B4+B9
B11:D11	B11	=B10/B5

Here is another example of that where Employee 3 just starts to get part of the bonus.

ToddMBaker.xlsm
	A	B	C	D	E	F	G
1	Bonus Pool	12,335
2
3	Employee	Employee 1	Employee 2	Employee 3	Q1 Totals	Q1+Bonus	Q1+Bonus (Relevant Employees)
4	Amount paid	65,000.00	44,000.00	56,000.00	165,000.00	177,335.00	177,335.00
5	Quarterly Target Pay	80,000.00	50,000.00	60,000.00	190,000.00		190,000.00
6	% of Target	81.25%	88.00%	93.33%		93.33%	93.33%
7
8
9	Total bonus	9,667.37	2,667.11	0.53	12,335.00
10	New Quarterly comp	74,667.37	46,667.11	56,000.53
11	New % of goal	93.33%	93.33%	93.33%
Sheet3

Cell Formulas
Range	Formula
F4	F4	=E4+B1
E4:E5,E9	E4	=SUM(B4:D4)
G4	G4	=SUMIF(B6:D6,"<"&F6,B4:D4)+B1
G5	G5	=SUMIF(B6:D6,"<"&F6,B5:D5)
B6:D6,G6	B6	=B4/B5
F6	F6	=F4/E5
B9:D9	B9	=IF(B6<$F$6,$G$6*B5-B4,0)
B10:D10	B10	=B4+B9
B11:D11	B11	=B10/B5

 

[ToddMBaker]

Perhaps something like this?

ToddMBaker.xlsm
	A	B	C	D	E	F	G
1	Bonus Pool	10,000
2
3	Employee	Employee 1	Employee 2	Employee 3	Q1 Totals	Q1+Bonus	Q1+Bonus (Relevant Employees)
4	Amount paid	65,000.00	44,000.00	56,000.00	165,000.00	175,000.00	119,000.00
5	Quarterly Target Pay	80,000.00	50,000.00	60,000.00	190,000.00		130,000.00
6	% of Target	81.25%	88.00%	93.33%		92.11%	91.54%
7
8
9	Total bonus	8,230.77	1,769.23	0.00	10,000.00
10	New Quarterly comp	73,230.77	45,769.23	56,000.00
11	New % of goal	91.54%	91.54%	93.33%
Sheet3

Cell Formulas
Range	Formula
F4	F4	=E4+B1
E4:E5,E9	E4	=SUM(B4:D4)
G4	G4	=SUMIF(B6:D6,"<"&F6,B4:D4)+B1
G5	G5	=SUMIF(B6:D6,"<"&F6,B5:D5)
B6:D6,G6	B6	=B4/B5
F6	F6	=F4/E5
B9:D9	B9	=IF(B6<$F$6,$G$6*B5-B4,0)
B10:D10	B10	=B4+B9
B11:D11	B11	=B10/B5

Here is another example of that where Employee 3 just starts to get part of the bonus.

ToddMBaker.xlsm
	A	B	C	D	E	F	G
1	Bonus Pool	12,335
2
3	Employee	Employee 1	Employee 2	Employee 3	Q1 Totals	Q1+Bonus	Q1+Bonus (Relevant Employees)
4	Amount paid	65,000.00	44,000.00	56,000.00	165,000.00	177,335.00	177,335.00
5	Quarterly Target Pay	80,000.00	50,000.00	60,000.00	190,000.00		190,000.00
6	% of Target	81.25%	88.00%	93.33%		93.33%	93.33%
7
8
9	Total bonus	9,667.37	2,667.11	0.53	12,335.00
10	New Quarterly comp	74,667.37	46,667.11	56,000.53
11	New % of goal	93.33%	93.33%	93.33%
Sheet3

Cell Formulas
Range	Formula
F4	F4	=E4+B1
E4:E5,E9	E4	=SUM(B4:D4)
G4	G4	=SUMIF(B6:D6,"<"&F6,B4:D4)+B1
G5	G5	=SUMIF(B6:D6,"<"&F6,B5:D5)
B6:D6,G6	B6	=B4/B5
F6	F6	=F4/E5
B9:D9	B9	=IF(B6<$F$6,$G$6*B5-B4,0)
B10:D10	B10	=B4+B9
B11:D11	B11	=B10/B5

Thank you for the help! This is definitely the outcome I want. I should have more specific about what happens if there isn’t enough bonus to make them all equal. In that case the lowest percent should increase until even with the next and then those two should increase together.

It seems the trick is in determining what the final percentages should be and going backwards.

This sheet almost works in all cases, but when the bonus pool is between 2200 and 5400 it ends up taking money away from the middle employee. We need to add some additional logic to not include that employee but I'm not sure off hand what it needs to be. I will continue working on this. So close!!

 

[ToddMBaker]

I got it to work by doing some error detection using IF statements in the bonus calculation. Basically, if the bonus calculation is less than zero, I set it to zero, and if it is greater than the total bonus pool, I make it equal to the bonus pool. I'm not sure if there is a cleaner way to do this by not including the employee in the calculation but this seems to work.

Bonus Pool Calculation - example.xlsx
	A	B	C	D	E	F	G
1	Bonus Pool	$ 5,000.00
2
3	Employee	Employee 1	Employee 2	Employee 3	Q1 Totals	Q1+Bonus	Q1+Bonus (Relevant Employees)
4	Amount paid	$ 65,000.00	$ 44,000.00	$ 56,000.00	$165,000.00	$170,000.00	$ 114,000.00
5	Quarterly Target Pay	$ 80,000.00	$ 50,000.00	$ 60,000.00	$190,000.00		$ 130,000.00
6	% of Target	81.25%	88.00000%	93.33%		89.47368%	87.69%
7
8
9	Total bonus	$ 5,000.00	$ -	$ -	$ 5,000.00
10	New Quarterly comp	$ 70,000.00	$ 44,000.00	$ 56,000.00
11	New % of goal	87.50%	88.00%	93.33%
Sheet2

Cell Formulas
Range	Formula
F4	F4	=E4+B1
E4:E5,E9	E4	=SUM(B4:D4)
G4	G4	=SUMIF(B6:D6,"<"&F6,B4:D4)+B1
G5	G5	=SUMIF(B6:D6,"<"&F6,B5:D5)
B6:D6,G6	B6	=B4/B5
F6	F6	=F4/E5
B9:D9	B9	=IF((IF(B6<$F$6,$G$6*B5-B4,0))<0,0,IF((IF(B6<$F$6,$G$6*B5-B4,0))>$B$1,$B$1,(IF(B6<$F$6,$G$6*B5-B4,0))))
B10:D10	B10	=B4+B9
B11:D11	B11	=B10/B5

 

[johnnyL]

If you set it up to where each employee always gets the same percentage of the bonus pool:

TextJoinAlternative.xlsm
	A	B	C	D	E	F
1	Bonus Pool	$25,000.00
2
3	Employee	Employee 1	Employee 2	Employee 3	Q1 Totals
4	Quarterly paid rate	$65,000.00	$44,000.00	$56,000.00	$165,000.00
5	Quarterly Target paid rate	$80,000.00	$50,000.00	$60,000.00	$190,000.00
6	% of Target	81.25%	88.00%	93.33%
7	Equilization amount	$9,666.67	$2,666.67	$0.00	$12,333.33
8	Split Remainder	$5,333.33	$3,333.33	$4,000.00	$12,666.67
9	Total bonus	$15,000.00	$6,000.00	$4,000.00	$25,000.00
10	Total bonusV2calculation	$15,000.00	$6,000.00	$4,000.00	$25,000.00
11	New Quarterly comp	$80,000.00	$50,000.00	$60,000.00
12	New % of goal	100.00%	100.00%	100.00%
13
Sheet5

Cell Formulas
Range	Formula
E8:E10,E4:E5	E4	=SUM(B4:D4)
B6:D6	B6	=B4/B5
B7:D7	B7	=(MAX($B$6:$D$6)-B6)*B5
E7	E7	=SUM(B7:C7)
B8:D8	B8	=IF((MIN(B5-B4-B7,(B4+B7)/($E$4+$E$7)*($B$1-$E$7)))>0,MIN(B5-B4-B7,(B4+B7)/($E$4+$E$7)*($B$1-$E$7)),0)
B9:D9	B9	=IF($E$7<$B$1,(B7+B8),((B7/SUM($B$7:$D$7))*$B$1))
B10	B10	=(B5 - B4) / ((B5 - B4) + (C5 - C4) + (D5 - D4)) * B1
C10	C10	=(C5 - C4) / ((B5 - B4) + (C5 - C4) + (D5 - D4)) * B1
D10	D10	=(D5 - D4) / ((B5 - B4) + (C5 - C4) + (D5 - D4)) * B1
B11:D11	B11	=B4+B9
B12:D12	B12	=B11/B5

Row 10 does that type of calculation.

 

[ToddMBaker]

Here is my final sheet. I brought the other calculations into the bonus calculation to cleanup the sheet. I am marking Peter's answer as the solution because it got me about 90% of the way there. Thank you guys!

Bonus Pool Calculation - example.xlsx
	A	B	C	D	E
1	Bonus Pool	$ 5,420.00
2
3	Employee	Employee 1	Employee 2	Employee 3	Q1 Totals
4	Amount paid	$ 65,000.00	$ 44,000.00	$ 56,000.00	$165,000.00
5	Quarterly Target Pay	$ 80,000.00	$ 50,000.00	$ 60,000.00	$190,000.00
6	% of Target	81.25%	88.00000%	93.33%
7	Total bonus	$ 5,412.31	$ 7.69	$ -	$ 5,420.00
8	New Quarterly comp	$ 70,412.31	$ 44,007.69	$ 56,000.00
9	New % of goal	88.02%	88.02%	93.33%
Sheet2

Cell Formulas
Range	Formula
E4:E5,E7	E4	=SUM(B4:D4)
B6:D6	B6	=B4/B5
B7	B7	=IF((IF(B6<((E4+B1)/E5),(SUMIF(B6:D6,"<"&((E4+B1)/E5),B4:D4)+B1)/(SUMIF(B6:D6,"<"&((E4+B1)/E5),B5:D5))*B5-B4,0))<0,0,IF((IF(B6<((E4+B1)/E5),(SUMIF(B6:D6,"<"&((E4+B1)/E5),B4:D4)+B1)/(SUMIF(B6:D6,"<"&((E4+B1)/E5),B5:D5))*B5-B4,0))>$B$1,$B$1,(IF(B6<((E4+B1)/E5),(SUMIF(B6:D6,"<"&((E4+B1)/E5),B4:D4)+B1)/(SUMIF(B6:D6,"<"&((E4+B1)/E5),B5:D5))*B5-B4,0))))
C7	C7	=IF((IF(C6<((E4+B1)/E5),(SUMIF(B6:D6,"<"&((E4+B1)/E5),B4:D4)+B1)/(SUMIF(B6:D6,"<"&((E4+B1)/E5),B5:D5))*C5-C4,0))<0,0,IF((IF(C6<((E4+B1)/E5),(SUMIF(B6:D6,"<"&((E4+B1)/E5),B4:D4)+B1)/(SUMIF(B6:D6,"<"&((E4+B1)/E5),B5:D5))*C5-C4,0))>$B$1,$B$1,(IF(C6<((E4+B1)/E5),(SUMIF(B6:D6,"<"&((E4+B1)/E5),B4:D4)+B1)/(SUMIF(B6:D6,"<"&((E4+B1)/E5),B5:D5))*C5-C4,0))))
D7	D7	=IF((IF(D6<((E4+B1)/E5),(SUMIF(B6:D6,"<"&((E4+B1)/E5),B4:D4)+B1)/(SUMIF(B6:D6,"<"&((E4+B1)/E5),B5:D5))*D5-D4,0))<0,0,IF((IF(D6<((E4+B1)/E5),(SUMIF(B6:D6,"<"&((E4+B1)/E5),B4:D4)+B1)/(SUMIF(B6:D6,"<"&((E4+B1)/E5),B5:D5))*D5-D4,0))>$B$1,$B$1,(IF(D6<((E4+B1)/E5),(SUMIF(B6:D6,"<"&((E4+B1)/E5),B4:D4)+B1)/(SUMIF(B6:D6,"<"&((E4+B1)/E5),B5:D5))*D5-D4,0))))
B8:D8	B8	=B4+B7
B9:D9	B9	=B8/B5

 

[Peter_SSs]

This sheet almost works in all cases, but when the bonus pool is between 2200 and 5400 it ends up taking money away from the middle employee.

Not sure this covers all circumstances But it is another one with some shorter formulas that you could test if you want.

ToddMBaker.xlsm
	A	B	C	D	E	F	G
1	Bonus Pool	3,000
2
3	Employee	Employee 1	Employee 2	Employee 3	Q1 Totals	Q1+Bonus	Q1+Bonus (Relevant Employees)
4	Amount paid	65,000.00	44,000.00	56,000.00	165,000.00	168,000.00	112,000.00
5	Quarterly Target Pay	80,000.00	50,000.00	60,000.00	190,000.00		130,000.00
6	% of Target	81.25%	88.00%	93.33%		88.42%	86.15%
7	Total bonus	3,000.00	0.00	0.00	3,000.00
8	New Quarterly comp	68,000.00	44,000.00	56,000.00
9	New % of goal	85.00%	88.00%	93.33%
Sheet5

Cell Formulas
Range	Formula
F4	F4	=E4+B1
E4:E5,E7	E4	=SUM(B4:D4)
G4	G4	=SUMIF(B6:D6,"<"&F6,B4:D4)+B1
G5	G5	=SUMIF(B6:D6,"<"&F6,B5:D5)
B6:D6,G6	B6	=B4/B5
F6	F6	=F4/E5
B7:D7	B7	=IF(B6<$G$6,MIN($G$6*B5-B4,$B$1),0)
B8:D8	B8	=B4+B7
B9:D9	B9	=B8/B5