Formula to divide total amount according to percentage of a target

T

ToddMBaker

New Member
Joined
Dec 17, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDE
1Bonus Pool$10,000.00
2
3EmployeeEmployee 1Employee 2Employee 3Q1 Totals
4Amount paid$65,000.00$44,000.00$56,000.00$165,000.00
5Quarterly Target Pay$80,000.00$50,000.00$60,000.00$190,000.00
6% of Target81.25%88.00%93.33%
7Equilization amount$9,666.67$2,666.67$0.00$12,333.33
8Split Remainder$0.00$0.00$0.00$0.00
9Total bonus$7,837.84$2,162.16$0.00$10,000.00
10New Quarterly comp$72,837.84$46,162.16$56,000.00
11New % of goal91.05%92.32%93.33%
2021 Compensation
Cell Formulas
RangeFormula
E4:E5,E8:E9E4=SUM(B4:D4)
B6:D6B6=B4/B5
B7:D7B7=(MAX($B$6:$D$6)-B6)*B5
E7E7=SUM(B7:C7)
B8:D8B8=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:D9B9=IF($E$7<$B$1,(B7+B8),((B7/SUM($B$7:$D$7))*$B$1))
B10:D10B10=B4+B9
B11:D11B11=B10/B5
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Welcome to the MrExcel board!

ToddMBaker said:
We want to split the bonus pool so that all employees end up with the same percentage of their target pay rate.
Click to expand...
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
ABCDE
1Bonus Pool10000
2
3EmployeeEmployee 1Employee 2Employee 3Q1 Totals
4Amount paid65,000.0044,000.0056,000.00165,000.00
5Quarterly Target Pay80,000.0050,000.0060,000.00190,000.00
6% of Target81.25%88.00%93.33%
7
8
9Total bonus8,684.212,052.63-736.8410,000.00
10New Quarterly comp73,684.2146,052.6355,263.16
11New % of goal92.11%92.11%92.11%
Sheet2
Cell Formulas
RangeFormula
E4:E5,E9E4=SUM(B4:D4)
B6:D6B6=B4/B5
B9:D9B9=($E$4+$B$1)/$E$5*B5-B4
B10:D10B10=B4+B9
B11:D11B11=B10/B5
 
Last edited:
Upvote 0
Peter_SSs said:
If you don't want to do that then what do you want to happen?
Click to expand...
Perhaps something like this?

ToddMBaker.xlsm
ABCDEFG
1Bonus Pool10,000
2
3EmployeeEmployee 1Employee 2Employee 3Q1 TotalsQ1+BonusQ1+Bonus (Relevant Employees)
4Amount paid65,000.0044,000.0056,000.00165,000.00175,000.00119,000.00
5Quarterly Target Pay80,000.0050,000.0060,000.00190,000.00130,000.00
6% of Target81.25%88.00%93.33%92.11%91.54%
7
8
9Total bonus8,230.771,769.230.0010,000.00
10New Quarterly comp73,230.7745,769.2356,000.00
11New % of goal91.54%91.54%93.33%
Sheet3
Cell Formulas
RangeFormula
F4F4=E4+B1
E4:E5,E9E4=SUM(B4:D4)
G4G4=SUMIF(B6:D6,"<"&F6,B4:D4)+B1
G5G5=SUMIF(B6:D6,"<"&F6,B5:D5)
B6:D6,G6B6=B4/B5
F6F6=F4/E5
B9:D9B9=IF(B6<$F$6,$G$6*B5-B4,0)
B10:D10B10=B4+B9
B11:D11B11=B10/B5



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

ToddMBaker.xlsm
ABCDEFG
1Bonus Pool12,335
2
3EmployeeEmployee 1Employee 2Employee 3Q1 TotalsQ1+BonusQ1+Bonus (Relevant Employees)
4Amount paid65,000.0044,000.0056,000.00165,000.00177,335.00177,335.00
5Quarterly Target Pay80,000.0050,000.0060,000.00190,000.00190,000.00
6% of Target81.25%88.00%93.33%93.33%93.33%
7
8
9Total bonus9,667.372,667.110.5312,335.00
10New Quarterly comp74,667.3746,667.1156,000.53
11New % of goal93.33%93.33%93.33%
Sheet3
Cell Formulas
RangeFormula
F4F4=E4+B1
E4:E5,E9E4=SUM(B4:D4)
G4G4=SUMIF(B6:D6,"<"&F6,B4:D4)+B1
G5G5=SUMIF(B6:D6,"<"&F6,B5:D5)
B6:D6,G6B6=B4/B5
F6F6=F4/E5
B9:D9B9=IF(B6<$F$6,$G$6*B5-B4,0)
B10:D10B10=B4+B9
B11:D11B11=B10/B5
 
Upvote 0
Solution
Peter_SSs said:
Perhaps something like this?

ToddMBaker.xlsm
ABCDEFG
1Bonus Pool10,000
2
3EmployeeEmployee 1Employee 2Employee 3Q1 TotalsQ1+BonusQ1+Bonus (Relevant Employees)
4Amount paid65,000.0044,000.0056,000.00165,000.00175,000.00119,000.00
5Quarterly Target Pay80,000.0050,000.0060,000.00190,000.00130,000.00
6% of Target81.25%88.00%93.33%92.11%91.54%
7
8
9Total bonus8,230.771,769.230.0010,000.00
10New Quarterly comp73,230.7745,769.2356,000.00
11New % of goal91.54%91.54%93.33%
Sheet3
Cell Formulas
RangeFormula
F4F4=E4+B1
E4:E5,E9E4=SUM(B4:D4)
G4G4=SUMIF(B6:D6,"<"&F6,B4:D4)+B1
G5G5=SUMIF(B6:D6,"<"&F6,B5:D5)
B6:D6,G6B6=B4/B5
F6F6=F4/E5
B9:D9B9=IF(B6<$F$6,$G$6*B5-B4,0)
B10:D10B10=B4+B9
B11:D11B11=B10/B5



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

ToddMBaker.xlsm
ABCDEFG
1Bonus Pool12,335
2
3EmployeeEmployee 1Employee 2Employee 3Q1 TotalsQ1+BonusQ1+Bonus (Relevant Employees)
4Amount paid65,000.0044,000.0056,000.00165,000.00177,335.00177,335.00
5Quarterly Target Pay80,000.0050,000.0060,000.00190,000.00190,000.00
6% of Target81.25%88.00%93.33%93.33%93.33%
7
8
9Total bonus9,667.372,667.110.5312,335.00
10New Quarterly comp74,667.3746,667.1156,000.53
11New % of goal93.33%93.33%93.33%
Sheet3
Cell Formulas
RangeFormula
F4F4=E4+B1
E4:E5,E9E4=SUM(B4:D4)
G4G4=SUMIF(B6:D6,"<"&F6,B4:D4)+B1
G5G5=SUMIF(B6:D6,"<"&F6,B5:D5)
B6:D6,G6B6=B4/B5
F6F6=F4/E5
B9:D9B9=IF(B6<$F$6,$G$6*B5-B4,0)
B10:D10B10=B4+B9
B11:D11B11=B10/B5
Click to expand...
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!! :)
 
Upvote 0
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
ABCDEFG
1Bonus Pool$ 5,000.00
2
3EmployeeEmployee 1Employee 2Employee 3Q1 TotalsQ1+BonusQ1+Bonus (Relevant Employees)
4Amount paid$ 65,000.00$ 44,000.00$ 56,000.00$165,000.00$170,000.00$ 114,000.00
5Quarterly Target Pay$ 80,000.00$ 50,000.00$ 60,000.00$190,000.00$ 130,000.00
6% of Target81.25%88.00000%93.33%89.47368%87.69%
7
8
9Total bonus$ 5,000.00$ -$ -$ 5,000.00
10New Quarterly comp$ 70,000.00$ 44,000.00$ 56,000.00
11New % of goal87.50%88.00%93.33%
Sheet2
Cell Formulas
RangeFormula
F4F4=E4+B1
E4:E5,E9E4=SUM(B4:D4)
G4G4=SUMIF(B6:D6,"<"&F6,B4:D4)+B1
G5G5=SUMIF(B6:D6,"<"&F6,B5:D5)
B6:D6,G6B6=B4/B5
F6F6=F4/E5
B9:D9B9=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:D10B10=B4+B9
B11:D11B11=B10/B5
 
Upvote 0
If you set it up to where each employee always gets the same percentage of the bonus pool:

TextJoinAlternative.xlsm
ABCDEF
1Bonus Pool$25,000.00
2
3EmployeeEmployee 1Employee 2Employee 3Q1 Totals
4Quarterly paid rate$65,000.00$44,000.00$56,000.00$165,000.00
5Quarterly Target paid rate$80,000.00$50,000.00$60,000.00$190,000.00
6% of Target81.25%88.00%93.33%
7Equilization amount$9,666.67$2,666.67$0.00$12,333.33
8Split Remainder$5,333.33$3,333.33$4,000.00$12,666.67
9Total bonus$15,000.00$6,000.00$4,000.00$25,000.00
10Total bonusV2calculation$15,000.00$6,000.00$4,000.00$25,000.00
11New Quarterly comp$80,000.00$50,000.00$60,000.00
12New % of goal100.00%100.00%100.00%
13
Sheet5
Cell Formulas
RangeFormula
E8:E10,E4:E5E4=SUM(B4:D4)
B6:D6B6=B4/B5
B7:D7B7=(MAX($B$6:$D$6)-B6)*B5
E7E7=SUM(B7:C7)
B8:D8B8=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:D9B9=IF($E$7<$B$1,(B7+B8),((B7/SUM($B$7:$D$7))*$B$1))
B10B10=(B5 - B4) / ((B5 - B4) + (C5 - C4) + (D5 - D4)) * B1
C10C10=(C5 - C4) / ((B5 - B4) + (C5 - C4) + (D5 - D4)) * B1
D10D10=(D5 - D4) / ((B5 - B4) + (C5 - C4) + (D5 - D4)) * B1
B11:D11B11=B4+B9
B12:D12B12=B11/B5


Row 10 does that type of calculation.
 
Upvote 0
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
ABCDE
1Bonus Pool$ 5,420.00
2
3EmployeeEmployee 1Employee 2Employee 3Q1 Totals
4Amount paid$ 65,000.00$ 44,000.00$ 56,000.00$165,000.00
5Quarterly Target Pay$ 80,000.00$ 50,000.00$ 60,000.00$190,000.00
6% of Target81.25%88.00000%93.33%
7Total bonus$ 5,412.31$ 7.69$ -$ 5,420.00
8New Quarterly comp$ 70,412.31$ 44,007.69$ 56,000.00
9New % of goal88.02%88.02%93.33%
Sheet2
Cell Formulas
RangeFormula
E4:E5,E7E4=SUM(B4:D4)
B6:D6B6=B4/B5
B7B7=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))))
C7C7=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))))
D7D7=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:D8B8=B4+B7
B9:D9B9=B8/B5
 
Upvote 0
ToddMBaker said:
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.
Click to expand...
Not sure this covers all circumstances But it is another one with some shorter formulas that you could test if you want.

ToddMBaker.xlsm
ABCDEFG
1Bonus Pool3,000
2
3EmployeeEmployee 1Employee 2Employee 3Q1 TotalsQ1+BonusQ1+Bonus (Relevant Employees)
4Amount paid65,000.0044,000.0056,000.00165,000.00168,000.00112,000.00
5Quarterly Target Pay80,000.0050,000.0060,000.00190,000.00130,000.00
6% of Target81.25%88.00%93.33%88.42%86.15%
7Total bonus3,000.000.000.003,000.00
8New Quarterly comp68,000.0044,000.0056,000.00
9New % of goal85.00%88.00%93.33%
Sheet5
Cell Formulas
RangeFormula
F4F4=E4+B1
E4:E5,E7E4=SUM(B4:D4)
G4G4=SUMIF(B6:D6,"<"&F6,B4:D4)+B1
G5G5=SUMIF(B6:D6,"<"&F6,B5:D5)
B6:D6,G6B6=B4/B5
F6F6=F4/E5
B7:D7B7=IF(B6<$G$6,MIN($G$6*B5-B4,$B$1),0)
B8:D8B8=B4+B7
B9:D9B9=B8/B5
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
How To Adjust Nested IFS Formula To Include An Additional Column's Condition?
Replies
5
Views
309
Fluff
Fluff
D
  • Solved
New formula for cumulative values
Replies
6
Views
142
djclements
djclements
R
  • Solved
Social Security Monthly Allowed
Replies
4
Views
284
Robert Wyatt
R
V
  • Question Question
Cumulative Deposits in Indian Banks and TDS thereon
Replies
1
Views
130
vsuram
V
K
  • Question Question
Populate With Criteria
Replies
3
Views
79
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

Share this page

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