# Sales Bonus Formula Help

#### dandeisel

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

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

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

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