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:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,952
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:

dandeisel

New Member
Joined
Feb 1, 2017
Messages
16
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.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,952
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.
 

Forum statistics

Threads
1,081,447
Messages
5,358,731
Members
400,509
Latest member
excellearner241

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top