Sales Bonus Formula Help

[dandeisel]

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


[TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl31, width: 52, bgcolor: transparent"]Cars
[/TD]
[TD="class: xl31, width: 51, bgcolor: transparent"]Bonus
[/TD]
[/TR]
[TR]
[TD="class: xl31, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl31, bgcolor: transparent, align: right"]0
[/TD]
[/TR]
[TR]
[TD="class: xl31, bgcolor: transparent, align: right"]14
[/TD]
[TD="class: xl31, bgcolor: transparent, align: right"]500
[/TD]
[/TR]
[TR]
[TD="class: xl31, bgcolor: transparent, align: right"]18
[/TD]
[TD="class: xl31, bgcolor: transparent, align: right"]750
[/TD]
[/TR]
[TR]
[TD="class: xl31, bgcolor: transparent, align: right"]20
[/TD]
[TD="class: xl31, bgcolor: transparent, align: right"]1000
[/TD]
[/TR]
[TR]
[TD="class: xl31, bgcolor: transparent, align: right"]22
[/TD]
[TD="class: xl31, bgcolor: transparent, align: right"]1250
[/TD]
[/TR]
[TR]
[TD="class: xl31, bgcolor: transparent, align: right"]25
[/TD]
[TD="class: xl31, bgcolor: transparent, align: right"]1500
[/TD]
[/TR]
[TR]
[TD="class: xl31, bgcolor: transparent, align: right"]99999
[/TD]
[TD="class: xl31, bgcolor: transparent, align: right"]1500
[/TD]
[/TR]
</tbody>[/TABLE]

=(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))


[TABLE="width: 534"]
<tbody>[TR]
[TD="class: xl200, width: 96, bgcolor: #00B0F0"]NEW
CAR
[/TD]
[TD="class: xl200, width: 96, bgcolor: #00B0F0"]NEW TRUCK
[/TD]
[TD="class: xl202, width: 96, bgcolor: #4472C4"]USED
CAR
[/TD]
[TD="class: xl202, width: 96, bgcolor: #4472C4"]USED TRUCK
[/TD]
[TD="class: xl204, width: 75, bgcolor: #44546A"]Sold
MA/HY
[/TD]
[TD="class: xl194, width: 75, bgcolor: yellow"]Total
[/TD]
[TD="class: xl196, width: 89, bgcolor: #B4C6E7"]Demo
Bonus
[/TD]
[TD="class: xl196, width: 89, bgcolor: #B4C6E7"]Unit
Bonus
[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]1.5
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]4
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]3.5
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]6
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]15
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$300
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$575
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]1
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]9
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]3
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]7
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]20
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$300
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$800
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]3.5
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]4
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]2
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]2.5
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]12
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$200
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]3
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]1
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]7
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]3
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]14
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$300
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$500
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]3
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]7
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]4
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]5
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]19
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$300
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$975
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]1
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]4
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]2
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]3.5
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]10.5
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]3
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]2.5
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]5
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]1
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]11.5
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]0
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]3
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]3
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]4
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]10
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$150
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]1
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]9.5
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]4
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]8.5
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33"]23
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$300
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$1,625
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]4
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]4
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]2.5
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]9
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]19.5
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$300
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$975
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]0
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]0
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]0
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]0
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]0
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]0
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]2
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]3
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]2.5
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]7.5
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]2
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]3
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]4
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]3.5
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]12.5
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$300
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]0
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]8.5
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]7
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]2
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]17.5
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$300
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$650
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]0
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]2
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]0
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]3
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]5
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]1
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]2
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]1
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]2.5
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]6.5
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[/TR]
[TR]
[TD="class: xl32, bgcolor: #00B0F0"]0
[/TD]
[TD="class: xl32, bgcolor: #00B0F0"]2.5
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]3
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]1
[/TD]
[TD="class: xl72, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]6.5
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[TD="class: xl49, bgcolor: #BDD7EE, align: right"]$-
[/TD]
[/TR]
[TR]
[TD="class: xl147, bgcolor: #00B0F0"]0
[/TD]
[TD="class: xl147, bgcolor: #00B0F0"]0
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]3
[/TD]
[TD="class: xl48, bgcolor: #4472C4"]3
[/TD]
[TD="class: xl148, bgcolor: #44546A"]0
[/TD]
[TD="class: xl33, bgcolor: yellow"]6
[/TD]
[TD="class: xl149, bgcolor: #BDD7EE"][/TD]
[TD="class: xl149, bgcolor: #BDD7EE"][/TD]
[/TR]
</tbody>[/TABLE]

Thanks guys for helping out. Please reach out if you need any additional info.

 

[Eric W]

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

	F	G	H	I	J	K	L	M	N	O	P	V	W	X	Y	Z
Cars	Bonus	New Vehicles	Qualifier

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #00B0F0"]NEW[/TD]
[TD="bgcolor: #00B0F0"]NEW TRUCK[/TD]
[TD="bgcolor: #4472C4"]USED[/TD]
[TD="bgcolor: #4472C4"]USED TRUCK[/TD]
[TD="bgcolor: #44546A"]Sold[/TD]
[TD="bgcolor: #FFFF00"]Total[/TD]
[TD="bgcolor: #B4C6E7"]Demo[/TD]
[TD="bgcolor: #B4C6E7"]Unit[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #00B0F0"]CAR[/TD]
[TD="bgcolor: #00B0F0, align: right"][/TD]
[TD="bgcolor: #4472C4"]CAR[/TD]
[TD="bgcolor: #4472C4, align: right"][/TD]
[TD="bgcolor: #44546A"]MA/HY[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #B4C6E7"]Bonus[/TD]
[TD="bgcolor: #B4C6E7"]Bonus[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #00B0F0, align: right"]1.5[/TD]
[TD="bgcolor: #00B0F0, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]3.5[/TD]
[TD="bgcolor: #4472C4, align: right"]6[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]15[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$575[/TD]
[TD="align: right"]575[/TD]
[TD="align: right"]575[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #00B0F0, align: right"]1[/TD]
[TD="bgcolor: #00B0F0, align: right"]9[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]7[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]20[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$800[/TD]
[TD="align: right"]1495[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100%[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #00B0F0, align: right"]3.5[/TD]
[TD="bgcolor: #00B0F0, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]2[/TD]
[TD="bgcolor: #4472C4, align: right"]2.5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]12[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$200[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]750[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]115%[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #00B0F0, align: right"]3[/TD]
[TD="bgcolor: #00B0F0, align: right"]1[/TD]
[TD="bgcolor: #4472C4, align: right"]7[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]14[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]130%[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #00B0F0, align: right"]3[/TD]
[TD="bgcolor: #00B0F0, align: right"]7[/TD]
[TD="bgcolor: #4472C4, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]19[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$975[/TD]
[TD="align: right"]1121.25[/TD]
[TD="align: right"]975[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #00B0F0, align: right"]1[/TD]
[TD="bgcolor: #00B0F0, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]2[/TD]
[TD="bgcolor: #4472C4, align: right"]3.5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]10.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #00B0F0, align: right"]3[/TD]
[TD="bgcolor: #00B0F0, align: right"]2.5[/TD]
[TD="bgcolor: #4472C4, align: right"]5[/TD]
[TD="bgcolor: #4472C4, align: right"]1[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]11.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]4[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]10[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$150[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #00B0F0, align: right"]1[/TD]
[TD="bgcolor: #00B0F0, align: right"]9.5[/TD]
[TD="bgcolor: #4472C4, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]8.5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="align: right"]23[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$1,625[/TD]
[TD="align: right"]1868.75[/TD]
[TD="align: right"]1625[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #00B0F0, align: right"]4[/TD]
[TD="bgcolor: #00B0F0, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]2.5[/TD]
[TD="bgcolor: #4472C4, align: right"]9[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]19.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$975[/TD]
[TD="align: right"]1121.25[/TD]
[TD="align: right"]975[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #4472C4, align: right"]0[/TD]
[TD="bgcolor: #4472C4, align: right"]0[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]0[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]2[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]2.5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]7.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #00B0F0, align: right"]2[/TD]
[TD="bgcolor: #00B0F0, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]3.5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]12.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]8.5[/TD]
[TD="bgcolor: #4472C4, align: right"]7[/TD]
[TD="bgcolor: #4472C4, align: right"]2[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]17.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$650[/TD]
[TD="align: right"]747.5[/TD]
[TD="align: right"]650[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]2[/TD]
[TD="bgcolor: #4472C4, align: right"]0[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #00B0F0, align: right"]1[/TD]
[TD="bgcolor: #00B0F0, align: right"]2[/TD]
[TD="bgcolor: #4472C4, align: right"]1[/TD]
[TD="bgcolor: #4472C4, align: right"]2.5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]6.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]2.5[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]1[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]6.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]6[/TD]
[TD="bgcolor: #BDD7EE, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N4[/TH]
[TD="align: left"]=(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))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]O4[/TH]
[TD="align: left"]=(VLOOKUP(K4,$W$4:$X$9,2) + MAX(0,K4-25)*100) * VLOOKUP(F4+G4,$Y$4:$Z$7,2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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.

 

[dandeisel]

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:

	F	G	H	I	J	K	L	M	N	O	P	V	W	X	Y	Z
Cars	Bonus	New Vehicles	Qualifier

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #00B0F0"]NEW[/TD]
[TD="bgcolor: #00B0F0"]NEW TRUCK[/TD]
[TD="bgcolor: #4472C4"]USED[/TD]
[TD="bgcolor: #4472C4"]USED TRUCK[/TD]
[TD="bgcolor: #44546A"]Sold[/TD]
[TD="bgcolor: #FFFF00"]Total[/TD]
[TD="bgcolor: #B4C6E7"]Demo[/TD]
[TD="bgcolor: #B4C6E7"]Unit[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #00B0F0"]CAR[/TD]
[TD="bgcolor: #00B0F0, align: right"][/TD]
[TD="bgcolor: #4472C4"]CAR[/TD]
[TD="bgcolor: #4472C4, align: right"][/TD]
[TD="bgcolor: #44546A"]MA/HY[/TD]
[TD="bgcolor: #FFFF00, align: right"][/TD]
[TD="bgcolor: #B4C6E7"]Bonus[/TD]
[TD="bgcolor: #B4C6E7"]Bonus[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #00B0F0, align: right"]1.5[/TD]
[TD="bgcolor: #00B0F0, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]3.5[/TD]
[TD="bgcolor: #4472C4, align: right"]6[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]15[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$575[/TD]
[TD="align: right"]575[/TD]
[TD="align: right"]575[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #00B0F0, align: right"]1[/TD]
[TD="bgcolor: #00B0F0, align: right"]9[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]7[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]20[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$800[/TD]
[TD="align: right"]1495[/TD]
[TD="align: right"]1300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100%[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #00B0F0, align: right"]3.5[/TD]
[TD="bgcolor: #00B0F0, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]2[/TD]
[TD="bgcolor: #4472C4, align: right"]2.5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]12[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$200[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]750[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]115%[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #00B0F0, align: right"]3[/TD]
[TD="bgcolor: #00B0F0, align: right"]1[/TD]
[TD="bgcolor: #4472C4, align: right"]7[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]14[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"]500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]1000[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]130%[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #00B0F0, align: right"]3[/TD]
[TD="bgcolor: #00B0F0, align: right"]7[/TD]
[TD="bgcolor: #4472C4, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]19[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$975[/TD]
[TD="align: right"]1121.25[/TD]
[TD="align: right"]975[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #00B0F0, align: right"]1[/TD]
[TD="bgcolor: #00B0F0, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]2[/TD]
[TD="bgcolor: #4472C4, align: right"]3.5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]10.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #00B0F0, align: right"]3[/TD]
[TD="bgcolor: #00B0F0, align: right"]2.5[/TD]
[TD="bgcolor: #4472C4, align: right"]5[/TD]
[TD="bgcolor: #4472C4, align: right"]1[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]11.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]4[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]10[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$150[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: #00B0F0, align: right"]1[/TD]
[TD="bgcolor: #00B0F0, align: right"]9.5[/TD]
[TD="bgcolor: #4472C4, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]8.5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="align: right"]23[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$1,625[/TD]
[TD="align: right"]1868.75[/TD]
[TD="align: right"]1625[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="bgcolor: #00B0F0, align: right"]4[/TD]
[TD="bgcolor: #00B0F0, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]2.5[/TD]
[TD="bgcolor: #4472C4, align: right"]9[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]19.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$975[/TD]
[TD="align: right"]1121.25[/TD]
[TD="align: right"]975[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #4472C4, align: right"]0[/TD]
[TD="bgcolor: #4472C4, align: right"]0[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]0[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]2[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]2.5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]7.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="bgcolor: #00B0F0, align: right"]2[/TD]
[TD="bgcolor: #00B0F0, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]4[/TD]
[TD="bgcolor: #4472C4, align: right"]3.5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]12.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]8.5[/TD]
[TD="bgcolor: #4472C4, align: right"]7[/TD]
[TD="bgcolor: #4472C4, align: right"]2[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]17.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$300[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$650[/TD]
[TD="align: right"]747.5[/TD]
[TD="align: right"]650[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]2[/TD]
[TD="bgcolor: #4472C4, align: right"]0[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="bgcolor: #00B0F0, align: right"]1[/TD]
[TD="bgcolor: #00B0F0, align: right"]2[/TD]
[TD="bgcolor: #4472C4, align: right"]1[/TD]
[TD="bgcolor: #4472C4, align: right"]2.5[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]6.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]2.5[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]1[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]6.5[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="bgcolor: #BDD7EE, align: right"]$-[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #00B0F0, align: right"]0[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #4472C4, align: right"]3[/TD]
[TD="bgcolor: #44546A, align: right"]0[/TD]
[TD="bgcolor: #FFFF00, align: right"]6[/TD]
[TD="bgcolor: #BDD7EE, align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]N4[/TH]
[TD="align: left"]=(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))[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]O4[/TH]
[TD="align: left"]=(VLOOKUP(K4,$W$4:$X$9,2) + MAX(0,K4-25)*100) * VLOOKUP(F4+G4,$Y$4:$Z$7,2)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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]

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.