# Calculate Bonus payouts per quarter - help!

#### HELENTR

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

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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

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

</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!!

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,626
Messages
5,838,430
Members
430,547
Latest member
jopshio

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

### Which adblocker are you using?

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

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