I have a difficult one, Well I think it's difficult PLEASE HELP!!!

[tommygunnz]

I'm trying to make a commission structure for Excel and it's pretty difficult

So we get paid 30% of the front gross + 10% of the back gross = total commission.

Example: $2,000 front = $600, and $2000 back =$200 grand total $800

if the front is negative, subtract that to the back gross x 10%.
if its below 250, you get paid 250 minimum. if its above 250, you the higher pay.

example $-2000 front & $4000 back = 10% x $2000= $200 id get $250 commission
example $-2000 front & $8000 back = 10% x $6000=$600 id get paid $600
example $-20 front & $4000 back=10% x $3980 id get paid $398
example $200 front (200*.3=60) & $4000 back (4000*.1=400) total paid $460

We will always get paid a minimum of $250 total.

any way someone can help me make an function for this?

 

[jtakw]

Hi,

This should do it:

Book1
	A	B	C	D
1	Front Gross	Back Gross		Commission
2	-$2,000.00	$4,000.00		$250.00
3	-$2,000.00	$8,000.00		$600.00
4	-$20.00	$4,000.00		$398.00
5	$200.00	$4,000.00		$460.00
Sheet1

Cell Formulas
Range	Formula
D2	=MAX(IF(A2<0,SUM(A2:B2)*0.1,SUM(A2*0.3,B2*0.1)),250)

D2 formula copied down, adjust cell references as needed.

 

[tommygunnz]

so my excel looks like this, but I can always change it
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 102px"><col width="76"><col width="74"><col width="75"><col width="69"><col width="63"></colgroup><tbody>[TR]
[TD="align: center"]Commission should be[/TD]
[TD="colspan: 2, align: center"] Front Gross [/TD]
[TD="colspan: 2, align: center"] Back Gross [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"] Gross [/TD]
[TD="align: center"] Commission [/TD]
[TD="align: center"] Gross [/TD]
[TD="align: center"] Commission [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]$485[/TD]
[TD="align: center"] $ 2,234.00 [/TD]
[TD="align: center"] $ 335.10 [/TD]
[TD="align: center"] $1500[/TD]
[TD="align: center"] $150[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]$250[/TD]
[TD="align: center"] $(2,042.00)[/TD]
[TD="align: center"] $ 250.00 [/TD]
[TD="align: center"] $ 1,566.00 [/TD]
[TD="align: center"] $ 156.60 [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]$536[/TD]
[TD="align: center"]$ 2,050.00 [/TD]
[TD="align: center"] $ 307.50 [/TD]
[TD="align: center"] $ 2,292.00 [/TD]
[TD="align: center"] $ 229.20 [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]$237[/TD]
[TD="align: center"]$ 28.62 [/TD]
[TD="align: center"] $ 250.00 [/TD]
[TD="align: center"] $ 7,907.00 [/TD]
[TD="align: center"] $ 790.70 [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]$946.7[/TD]
[TD="align: center"]$ 2,614.00 [/TD]
[TD="align: center"] $ 392.10 [/TD]
[TD="align: center"] $ 5,546.00 [/TD]
[TD="align: center"] $ 554.60 [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]$250
[/TD]
[TD="align: center"]$(2,178.00)[/TD]
[TD="align: center"] $ 250.00 [/TD]
[TD="align: center"] $ 3,720.00 [/TD]
[TD="align: center"] $ 372.00 [/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

 

[Intronaut]

Assuming front is in A1 and back is in A2:

=IF(A1>=0,MAX(0.3*A1+0.1*B1,250),MAX(0.1*SUM(B1,A1),250))

HTH

 

[Intronaut]

Sorry I meant if front is in a1 and back is in b1. But jtakw already addressed so never mind!

 

[tommygunnz]

WOW!!! Fantastic!!

I forgot one thing! I Thank you so so much for taking the time to do this for me.

but sometime the deals are split in half. but only the front is split so it would be .15 not .3 the back is always 10%
my spreed sheet looks like this
a b c d e f g h i j k l
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 20px"><col width="34"><col width="37"><col width="36"><col width="63"><col width="111"><col width="102"><col width="76"><col width="75"><col width="63"><col width="55"><col width="35"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]Date[/TD]
[TD]Used[/TD]
[TD]New[/TD]
[TD]Stock #[/TD]
[TD]Vehicle[/TD]
[TD]Customer[/TD]
[TD="align: center"] Front Gross [/TD]
[TD="align: center"] Back Gross [/TD]
[TD="align: center"] Deal # [/TD]
[TD="align: center"]Vin[/TD]
[TD="align: center"]RDR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] Gross [/TD]
[TD="align: center"] Gross [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]12/15[/TD]
[TD][/TD]
[TD]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] $ 2,234.00 [/TD]
[TD="align: center"] $ - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]12/19[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] $ (2,042.00)[/TD]
[TD="align: center"] $ 1,566.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]12/21[/TD]
[TD]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] $ 2,050.00 [/TD]
[TD="align: center"] $ 2,292.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]12/22[/TD]
[TD="align: center"][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] $ 28.62 [/TD]
[TD="align: center"] $ 7,907.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]12/23[/TD]
[TD="align: center"][/TD]
[TD]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] $ 2,614.00 [/TD]
[TD="align: center"] $ 5,546.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]12/26[/TD]
[TD]1[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] $ (2,178.00)[/TD]
[TD="align: center"] $ 3,720.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]12/27[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] $ (794.00)[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]12/27[/TD]
[TD]1[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] $ (2,895.00)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]12/27[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] $ 3,033.00 [/TD]
[TD="align: center"] $ 3,329.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]12/28[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] $ 624.00 [/TD]
[TD="align: center"] $ 4,700.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]12/30[/TD]
[TD="align: center"][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] $ (2,262.00)[/TD]
[TD="align: center"] $ - [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]12/30[/TD]
[TD]0.5[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] $ (1,560.00)[/TD]
[TD="align: center"] $ 1,000.00 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What i was originally doing was telling the front gross to multiply but by C1 or D1 to get either a full or half commission.

is this possible?

 

[tommygunnz]

WOW!!! Fantastic!!

I forgot one thing! I Thank you so so much for taking the time to do this for me.

but sometime the deals are split in half. but only the front is split so it would be .15 not .3 the back is always 10%
my spreed sheet looks like this
a b c d e f g h i j k l
[TABLE="width: 0"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]Date[/TD]
[TD]Used[/TD]
[TD]New[/TD]
[TD]Stock #[/TD]
[TD]Vehicle[/TD]
[TD]Customer[/TD]
[TD="align: center"]Front Gross[/TD]
[TD="align: center"]Back Gross[/TD]
[TD="align: center"]Deal #[/TD]
[TD="align: center"]Vin[/TD]
[TD="align: center"]RDR[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]Gross[/TD]
[TD="align: center"]Gross[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]12/15[/TD]
[TD][/TD]
[TD]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ 2,234.00[/TD]
[TD="align: center"]$ -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]12/19[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ (2,042.00)[/TD]
[TD="align: center"]$ 1,566.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]12/21[/TD]
[TD]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ 2,050.00[/TD]
[TD="align: center"]$ 2,292.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]12/22[/TD]
[TD="align: center"][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ 28.62[/TD]
[TD="align: center"]$ 7,907.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]12/23[/TD]
[TD="align: center"][/TD]
[TD]0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ 2,614.00[/TD]
[TD="align: center"]$ 5,546.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]12/26[/TD]
[TD]1[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ (2,178.00)[/TD]
[TD="align: center"]$ 3,720.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]12/27[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ (794.00)[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]12/27[/TD]
[TD]1[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ (2,895.00)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]12/27[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ 3,033.00[/TD]
[TD="align: center"]$ 3,329.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]12/28[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ 624.00[/TD]
[TD="align: center"]$ 4,700.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: center"]12/30[/TD]
[TD="align: center"][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ (2,262.00)[/TD]
[TD="align: center"]$ -[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: center"]12/30[/TD]
[TD]0.5[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$ (1,560.00)[/TD]
[TD="align: center"]$ 1,000.00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What i was originally doing was telling the front gross to multiply but by C1 or D1 to get either a full or half commission.

is this possible?

 

[jtakw]

Hi,

Is this what you mean?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Used	New	Stock #	Vehicle	Customer	Front Gross	Back Gross	Deal #	Vin	RDR	Commission
2	15-Dec		0.5				$2,234.00					335.1
3	19-Dec		1				($2,042.00)	$1,566.00				250
4	21-Dec	0.5					$2,050.00	$2,292.00				536.7
5	22-Dec		1				$28.62	$7,907.00				799.286
6	23-Dec		0.5				$2,614.00	$5,546.00				946.7
7	26-Dec	1					($2,178.00)	$3,720.00				250
8	27-Dec		1				($794.00)					250
9	27-Dec	1					($2,895.00)					250
10	27-Dec		1				$3,033.00	$3,329.00				1242.8
11	28-Dec		1				$624.00	$4,700.00				657.2
12	30-Dec		1				($2,262.00)					250
13	30-Dec	0.5					($1,560.00)	$1,000.00				250
Sheet4

Cell Formulas
Range	Formula
L2	=MAX(IF(G2<0,SUM(G2:H2)*0.1,SUM(G2*IF(OR(B2=0.5,C2=0.5),0.15,0.3),H2*0.1)),250)

L2 formula copied down.

 

[tommygunnz]

This works fantastic!!! Your awesome!!




When I add the formula, all my fields show a $250 commission, when I haven't added anything to the row. How can it show blank or 0 till I add something?

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 20px"><col width="34"><col width="37"><col width="36"><col width="63"><col width="111"><col width="102"><col width="76"><col width="75"><col width="96"></colgroup><tbody>[TR]
[TD="align: center"]17[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$250.00[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$250.00[/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$250.00[/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]$250.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

This will leave the "Commission" column BLANK if there are no entries in columns G and H (Front Gross and Back Gross):

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date	Used	New	Stock #	Vehicle	Customer	Front Gross	Back Gross	Deal #	Vin	RDR	Commission
2	15-Dec		0.5				$2,234.00					335.1
3	19-Dec		1				($2,042.00)	$1,566.00				250
4	21-Dec	0.5					$2,050.00	$2,292.00				536.7
5	22-Dec		1				$28.62	$7,907.00				799.286
6	23-Dec		0.5				$2,614.00	$5,546.00				946.7
7	26-Dec	1					($2,178.00)	$3,720.00				250
8	27-Dec		1				($794.00)					250
9	27-Dec	1					($2,895.00)					250
10	27-Dec		1				$3,033.00	$3,329.00				1242.8
11	28-Dec		1				$624.00	$4,700.00				657.2
12	30-Dec		1				($2,262.00)					250
13	30-Dec	0.5					($1,560.00)	$1,000.00				250
14	31-Dec
Sheet4

Cell Formulas
Range	Formula
L2	=IF(AND(G2="",H2=""),"",MAX(IF(G2<0,SUM(G2:H2)*0.1,SUM(G2*IF(OR(B2=0.5,C2=0.5),0.15,0.3),H2*0.1)),250))

L2 formula copied down.