Hey Guys,
I'm stuck and need some help.
I need a formula to calculate a sales Bonus
Here's the Pay plan & Qualifier:
14 UNITS = $500 BONUS*<o></o>
18 UNITS = $750 BONUS*<o></o>
20 UNITS = $1,000 BONUS*<o></o>
22 UNITS = $1,250 BONUS*<o></o>
25 UNITS = $1,500 BONUS*<o></o>
$100 BONUS PER CAR OVER 25 UNITS
QUALIFIERS
3 NEW VEHICLEDELIVERIES= 100% OF BONUS<o></o>
5 NEW VEHICLEDELIVERIES = 115% OF BONUS<o></o>
8 NEW VEHICLEDELIVERIES = 130% OF BONUS
Column F has New Cars Sold
Column G has New Trucks Sold
Column K has Total Sold
Column M is where the formula will go
Keep in mind we need to add both Column F & G to come up with the qualifiers
Here is currently what I'm using but something in the formula is not right.
I'm using a VLOOKUP formula and putting this in columns W & X and I'm hiding the columns
<tbody>
</tbody>
=(VLOOKUP(K5,$W$4:$X$11,2,TRUE) + (IF(K5>25,(K5-25)*100,0))) * (IF(F5+G5>=5,115%,1)) * (IF(F5+G5>=8,130%,1)) * (IF(F5+G5<2,0,1))* (IF(F5+G5>=3,1,1))
<tbody>
</tbody>
Thanks guys for helping out. Please reach out if you need any additional info.
I'm stuck and need some help.
I need a formula to calculate a sales Bonus
Here's the Pay plan & Qualifier:
14 UNITS = $500 BONUS*<o></o>
18 UNITS = $750 BONUS*<o></o>
20 UNITS = $1,000 BONUS*<o></o>
22 UNITS = $1,250 BONUS*<o></o>
25 UNITS = $1,500 BONUS*<o></o>
$100 BONUS PER CAR OVER 25 UNITS
QUALIFIERS
3 NEW VEHICLEDELIVERIES= 100% OF BONUS<o></o>
5 NEW VEHICLEDELIVERIES = 115% OF BONUS<o></o>
8 NEW VEHICLEDELIVERIES = 130% OF BONUS
Column F has New Cars Sold
Column G has New Trucks Sold
Column K has Total Sold
Column M is where the formula will go
Keep in mind we need to add both Column F & G to come up with the qualifiers
Here is currently what I'm using but something in the formula is not right.
I'm using a VLOOKUP formula and putting this in columns W & X and I'm hiding the columns
Cars | Bonus |
0 | 0 |
14 | 500 |
18 | 750 |
20 | 1000 |
22 | 1250 |
25 | 1500 |
99999 | 1500 |
<tbody>
</tbody>
=(VLOOKUP(K5,$W$4:$X$11,2,TRUE) + (IF(K5>25,(K5-25)*100,0))) * (IF(F5+G5>=5,115%,1)) * (IF(F5+G5>=8,130%,1)) * (IF(F5+G5<2,0,1))* (IF(F5+G5>=3,1,1))
NEW CAR | NEW TRUCK | USED CAR | USED TRUCK | Sold MA/HY | Total | Demo Bonus | Unit Bonus |
1.5 | 4 | 3.5 | 6 | 0 | 15 | $300 | $575 |
1 | 9 | 3 | 7 | 0 | 20 | $300 | $800 |
3.5 | 4 | 2 | 2.5 | 0 | 12 | $200 | $- |
3 | 1 | 7 | 3 | 0 | 14 | $300 | $500 |
3 | 7 | 4 | 5 | 0 | 19 | $300 | $975 |
1 | 4 | 2 | 3.5 | 0 | 10.5 | $- | $- |
3 | 2.5 | 5 | 1 | 0 | 11.5 | $- | $- |
0 | 3 | 3 | 4 | 0 | 10 | $150 | $- |
1 | 9.5 | 4 | 8.5 | 0 | 23 | $300 | $1,625 |
4 | 4 | 2.5 | 9 | 0 | 19.5 | $300 | $975 |
0 | 0 | 0 | 0 | 0 | 0 | $- | $- |
0 | 2 | 3 | 2.5 | 0 | 7.5 | $- | $- |
2 | 3 | 4 | 3.5 | 0 | 12.5 | $300 | $- |
0 | 8.5 | 7 | 2 | 0 | 17.5 | $300 | $650 |
0 | 2 | 0 | 3 | 0 | 5 | $- | $- |
1 | 2 | 1 | 2.5 | 0 | 6.5 | $- | $- |
0 | 2.5 | 3 | 1 | 0 | 6.5 | $- | $- |
0 | 0 | 3 | 3 | 0 | 6 |
<tbody>
</tbody>
Thanks guys for helping out. Please reach out if you need any additional info.
Last edited: