# Calculate Bonus payouts per quarter - help!

#### HELENTR

##### New Member
Hello Excel guru's

Please help! I'm going mad trying to figure this out.....!

I'm trying to work out how to allocate bonuses per quarter out of a pool of money:

1) In Q1 x 2 individuals achieved their targets - although at different percentages.
So I need to pay out the pool money of \$6828 in Q1 to the 2 individuals.
BUT the tricky part is that the person who achieved between 125-150% needs to get more than the person who achieved 110-120%

2) In Q2 x 3 individuals achieved their targets - at different percentages
So I need to pay out the pool money of \$7063 in Q2 to the 3 individuals.
BUT the tricky part is that the person who achieved 150%+ needs to get more than the 2 people who achieved 110-120%

3) In Q3 x 3 individuals achieved their targets - at different percentages
So I need to pay out the pool money of \$3922 in Q3 to the 3 individuals.
BUT the tricky part is that the person who achieved 150%+ needs to get more than the person who achieved 110-120% and who needs to get more than the person who achieved 100-110%

-- removed inline image ---

 Q1 Q2 Q3 payout pool \$ 6,828 \$ 7,063 \$ 3,922 Count payout Count payout Count payout 100% 110% 1 110% 120% 1 2 1 120% 150% 1 150% 1 1 2 3 3

<tbody>
</tbody>

So I think I have to work out the weightings for each category that gets at least a "1" (so Q1 is 50% and 50%) but don't know how to weight it further to account for the tiered weighting....

Hope someone can help?
Many thanks!
Helen

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### StephenCrump

##### MrExcel MVP
Welcome to the Forum!

Try this:

D4: =B4*TotalBonus/SUMPRODUCT(B\$4:B\$7,C\$4:C\$7)
E4: =C4*D4

You can vary the relative weights (highlighted) depending on how much you want to tilt the bonus pool to the higher performers.

ABCDE
1TotalBonus\$6,828
2
3%TargetWeightNumberAmount eachTotal
4100-1101.00\$2,905.53\$0.00
5110-1201.101\$3,196.09\$3,196.09
6120-1501.251\$3,631.91\$3,631.91
7150+1.75\$5,084.68\$0.00
8\$6,828.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

#### HELENTR

##### New Member
Welcome to the Forum!

Try this:

D4: =B4*TotalBonus/SUMPRODUCT(B\$4:B\$7,C\$4:C\$7)
E4: =C4*D4

You can vary the relative weights (highlighted) depending on how much you want to tilt the bonus pool to the higher performers.

ABCDE
1TotalBonus\$6,828
2
3%TargetWeightNumberAmount eachTotal
4100-1101.00\$2,905.53\$0.00
5110-1201.101\$3,196.09\$3,196.09
6120-1501.251\$3,631.91\$3,631.91
7150+1.75\$5,084.68\$0.00
8\$6,828.00

<tbody>
</tbody>

Oh my goodness - I knew it was something simple enough but just couldn't get it.

Thank you so much Stephen this works perfectly!!

Replies
2
Views
194
Replies
0
Views
458
Replies
3
Views
418
Replies
2
Views
323
Replies
0
Views
246

Threads
1,128,108
Messages
5,628,753
Members
416,335
Latest member
MA242

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

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