I need to create a formula for a tier pay system

[guy0nthec0uch]

Hey guys, I really try not to bother you all but this one is driving me nuts.

I work at a car dealership and I'm tasked with paying our BDC reps based on the units they sell.

So, I've factored a spreadsheet to calculate each of their sold but I need to pay them based on the following parameters.

15 SOLD = 250
20 SOLD = 350
25 SOLD = 500
30 SOLD = 750
35 SOLD = 1000

So, I need a formula that can take the cell used to calculate the sold and base the commission on the total.

I need this total to reflect under the DEALER SPIFF for each. Ideas?

 

[guy0nthec0uch]

What error message are you getting?

It's a formula parse error

 

[guy0nthec0uch]

You are not answering the question.
Either you need a tiered calculation of you need a lookup of the rate.
If you provide the result that you expect for a few examples such as 22 and 28, I will provide a suggestion.
Otherwise good luck.

I explained exactly how they are paid. What does 22 or 28 have to do with it?

If they sell 15 cars, they get a $250 bonus. If that number reaches 20, it becomes $350. If they end up reaching 25, it becomes a $500 bonus. I'm not sure where I'm losing you here. The commission only changes when they hit their tier number for the month.

 

[Candyman8019]

Are you able to share a copy of your workbook using onedrive or dropbox for us to take a look at. I can't understand why that formula would give you a parse error.

This works in my sheet:

Book1.xlsm
	G	H	I
11	Column1	Column2	Column3
12	5		#N/A
13	11		#N/A
14	15		250
15	17		250
16	32		750
17	35		1000
ChangeOrders

Cell Formulas
Range	Formula
I12:I17	I12	=LOOKUP(G12,{15,20,25,30,35},{250,350,500,750,1000})

 

[Candyman8019]

Also, to get rid of the N/A you could amend the formula to the following: =LOOKUP(G12,{0,15,20,25,30,35},{0,250,350,500,750,1000})

 

[Dave Patton]

Commissions 2023.xlsm
	A	B	C	D
1	Units	Rates
2	0	0
3	15	250
4	20	350
5	25	500
6	30	750
7	35	1000
8
9	Units Sold		or	Candyman's
10	10	0	0	0
11	16	250	250	250
12	22	350	350	350
13	28	500	500	500
14
1e

Cell Formulas
Range	Formula
B10:B13	B10	=LOOKUP(A10,$A$2:$B$7)
C10:C13	C10	=LOOKUP(A10,{0,0;15,250;20,350;25,500;30,750;35,1000})
D10:D13	D10	=LOOKUP(A10,{0,15,20,25,30,35},{0,250,350,500,750,1000})

 

[jross20]

This is my first time using the mini sheet thing, but here is my take on it. There might be an easier way but I've only had one coffee today...

Tier Thing.xlsx
	A	B	C	D	E	F	G	H	I	J
1					Tiers
2	Qty	Bonus		# Sold from Input	1	2	3	4	5	Final
3	15	$ 250.00		16	$ 250.00					$ 250.00
4	20	$ 350.00		32	$ 250.00	$ 350.00	$ 500.00	$ 750.00		$ 750.00
5	25	$ 500.00		5						$ -
6	30	$ 750.00		28	$ 250.00	$ 350.00	$ 500.00			$ 500.00
7	35	$ 1,000.00		9						$ -
8				40	$ 250.00	$ 350.00	$ 500.00	$ 750.00	$ 1,000.00	$ 1,000.00
9										$ -
10										$ -
11										$ -
12										$ -
13										$ -
14										$ -
15										$ -
16										$ -
17										$ -
18										$ -
19										$ -
20										$ -
21										$ -
22										$ -
23										$ -
24										$ -
25										$ -
26										$ -
27										$ -
28										$ -
29										$ -
30										$ -
31										$ -
32										$ -
33										$ -
34										$ -
35										$ -
36										$ -
37										$ -
38										$ -
39										$ -
40										$ -
41										$ -
42										$ -
43										$ -
44										$ -
45										$ -
46										$ -
47										$ -
48										$ -
49										$ -
50										$ -
51										$ -
PULL_CALC

Cell Formulas
Range	Formula
D3:D51	D3	=LET(var,Input!B2,IF(var="","",var))
E3:E51	E3	=IFERROR((INDEX(B:B,MATCH(LET(var,IF($D3>$A$3,$A$3,""),IF([@['# Sold from Input]]="","",var)),A:A,0))),"")
F3:F51	F3	=IFERROR((LET(var,INDEX(B:B,(MATCH((IF($D3>$A$4,$A$4,"")),A:A,0))),IF([@['# Sold from Input]]="","",var))),"")
G3:G51	G3	=IFERROR(LET(var,INDEX(B:B,MATCH((IF($D3>$A$5,$A$5,"")),A:A,0)),IF([@['# Sold from Input]]="","",var)),"")
H3:H51	H3	=IFERROR(LET(var,INDEX(B:B,MATCH(IF($D3>$A$6,$A$6,""),A:A,0)),IF([@['# Sold from Input]]="","",var)),"")
I3:I51	I3	=IFERROR(LET(var,(INDEX(B:B,MATCH((IF($D3>$A$7,$A$7,"")),A:A,0))),IF([@['# Sold from Input]]="","",var)),"")
J3:J51	J3	=MAX(Table3[@[1]:[5]])

 

[guy0nthec0uch]

Thanks for all the help guys. I've been slammed at work but will try your suggestions when I get home tonight. I will let you know how it goes!

 

[jross20]

Thanks for all the help guys. I've been slammed at work but will try your suggestions when I get home tonight. I will let you know how it goes!

Good luck!

Also...Do you usually try to do everything on one sheet or do you create like background calculation sheets? Just curious what others do

 

[guy0nthec0uch]

Hey guys, I figured it out!

I just needed to do an "IF" function.

So in this instance it was =IF(f10>24,450,IF(F10>19,350,IF(F10>14,250))) ETC..

 

[jross20]

Hey guys, I figured it out!

I just needed to do an "IF" function.

So in this instance it was =IF(f10>24,450,IF(F10>19,350,IF(F10>14,250))) ETC..

Ah a stacked if... I actually use this all the time in QuickBase but never thought to try it in Excel... haha. Sometimes the easiest solutions are the hardest to think of.