Sales Bonus Formula Help

dandeisel

New Member
Joined
Feb 1, 2017
Messages
16
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:p></o:p>
18 UNITS = $750 BONUS*<o:p></o:p>
20 UNITS = $1,000 BONUS*<o:p></o:p>
22 UNITS = $1,250 BONUS*<o:p></o:p>
25 UNITS = $1,500 BONUS*<o:p></o:p>
$100 BONUS PER CAR OVER 25 UNITS

QUALIFIERS
3 NEW VEHICLEDELIVERIES= 100% OF BONUS<o:p></o:p>
5 NEW VEHICLEDELIVERIES = 115% OF BONUS<o:p></o:p>
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:

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
I'm a little hazy on your requirements still, but if I'm close, then this might be what you want:

FGHIJKLMNOPVWXYZ
1NEWNEW TRUCKUSEDUSED TRUCKSoldTotalDemoUnit
2CARCARMA/HYBonusBonus
3CarsBonusNew VehiclesQualifier
41.543.56015$300$5755755750000
51937020$300$80014951300145003100%
63.5422.5012$200$-00187505115%
73173014$300$5005005002010008130%
83745019$300$9751121.25975221250
91423.5010.5$-$-00251500
1032.551011.5$-$-00
110334010$150$-00
1219.548.5023$300$1,6251868.751625
13442.59019.5$300$9751121.25975
14000000$-$-00
150232.507.5$-$-00
162343.5012.5$300$-00
1708.572017.5$300$650747.5650
18020305$-$-00
191212.506.5$-$-00
2002.53106.5$-$-00
2100330600

<tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
N4=(VLOOKUP(K4,$W$4:$X$9,2,TRUE) + (IF(K4>25,(K4-25)*100,0))) * (IF(F4+G4>=5,115%,1)) * (IF(F4+G4>=8,130%,1)) * (IF(F4+G4<2,0,1))* (IF(F4+G4>=3,1,1))
O4=(VLOOKUP(K4,$W$4:$X$9,2) + MAX(0,K4-25)*100) * VLOOKUP(F4+G4,$Y$4:$Z$7,2)

<tbody>
</tbody>

<tbody>
</tbody>



I built another table with the qualifiers in it. Your formula I put in N4, and mine in O4. Mine seems to jibe with your column M (except for row 5, but I think mine is correct).

Let me know if this helps.
 
Last edited:
Upvote 0
Eric! It worked my friend! Thank you so much for your help!
You have no idea how I was wrecking my head trying to get this right.

Eric I have another formula that was working great but we made some revision to one of the sheets it was pulling the data from and now it's throwing off the information. I'm sure your familiar with an Index-Match Functions! The issue i'm having is there are 3 tabs in 1 spreadsheet that I need to INDEX 1 column from and MATCH 3 columns as well to return the original indexed column. I probably just confused you:eek:. LOL! This would be so much easier if I can email you the 2 spreadsheets and then I can explain what I'm trying to accomplish. Please let me know if that is something you can help me with. If not I understand and Thank you again for your help on this formula.


I'm a little hazy on your requirements still, but if I'm close, then this might be what you want:

FGHIJKLMNOPVWXYZ
1NEWNEW TRUCKUSEDUSED TRUCKSoldTotalDemoUnit
2CARCARMA/HYBonusBonus
3CarsBonusNew VehiclesQualifier
41.543.56015$300$5755755750000
51937020$300$80014951300145003100%
63.5422.5012$200$-00187505115%
73173014$300$5005005002010008130%
83745019$300$9751121.25975221250
91423.5010.5$-$-00251500
1032.551011.5$-$-00
110334010$150$-00
1219.548.5023$300$1,6251868.751625
13442.59019.5$300$9751121.25975
14000000$-$-00
150232.507.5$-$-00
162343.5012.5$300$-00
1708.572017.5$300$650747.5650
18020305$-$-00
191212.506.5$-$-00
2002.53106.5$-$-00
2100330600

<tbody>
</tbody>
Sheet4

Worksheet Formulas
CellFormula
N4=(VLOOKUP(K4,$W$4:$X$9,2,TRUE) + (IF(K4>25,(K4-25)*100,0))) * (IF(F4+G4>=5,115%,1)) * (IF(F4+G4>=8,130%,1)) * (IF(F4+G4<2,0,1))* (IF(F4+G4>=3,1,1))
O4=(VLOOKUP(K4,$W$4:$X$9,2) + MAX(0,K4-25)*100) * VLOOKUP(F4+G4,$Y$4:$Z$7,2)

<tbody>
</tbody>

<tbody>
</tbody>



I built another table with the qualifiers in it. Your formula I put in N4, and mine in O4. Mine seems to jibe with your column M (except for row 5, but I think mine is correct).

Let me know if this helps.
 
Upvote 0
I'm glad the first formula is working for you.

As far as the second question, part of the benefit of this forum is that others can see and learn from the questions, and if we go offline to answer a question, we lose that. So first see if you can show a sample of your sheets here, with the expected and actual results.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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