EXCEL Formula for Commission

[vantage1]

I'm having problems constructing a formula for the following commission:

$200 and Under gets a 38% commission

plus (over $200)

$200 to $500 gets a 33% commission

plus if over $500 gets a 28% commission

Example #1: $355
$200 x .38 = $76 (first $200)
+
$150 x .33 = $49.50
=
TOTAL - $125.50

Example #2: $550
$200 x .38 = $76 (first $200)
+
$300 x .33 = $99 (next $300)
+
$50 x .28 = $14
=
TOTAL = $189

Can anyone help me with this formula?

Thank you so much.
Daryl Burns
626-303-0767

[BenjieLop]

=if(A1<=200,A1*38%,if(and(A1>200,A1<=500),76+(A1-200)*33%,175+(A1-500)*28%))

I'm having problems constructing a formula for the following commission:

$200 and Under gets a 38% commission

plus (over $200)

$200 to $500 gets a 33% commission

plus if over $500 gets a 28% commission

Example #1: $355
$200 x .38 = $76 (first $200)
+
$150 x .33 = $49.50
=
TOTAL - $125.50

Example #2: $550
$200 x .38 = $76 (first $200)
+
$300 x .33 = $99 (next $300)
+
$50 x .28 = $14
=
TOTAL = $189

Can anyone help me with this formula?

Thank you so much.
Daryl Burns
626-303-0767

[richardreye]

I personally like to use a table for the different commissions as this allows for it to be adjusted rather easily. Assuming your commission is based on sales, place these headings across 3 columns (I have used A1 as my starting point in this example).

Sales Commission Bound
0 38%
200 33% 76
500 28% 175

The entries under the title 'Bound' are from formulas starting at the second row of this title. the formula is =(A3-A2)*B2+C2 to produce 76. Copy this down.

The formula for working out the commission is as follows assuming that the sale amount is in Cell A8

=VLOOKUP(A8,A2:C6,3)+(A8-VLOOKUP(A8,A2:C6,1))*VLOOKUP(A8,A2:C6,2)

I extended the VLOOKUP range to row 6 to allow for future addings to this table (hence the use of a table).

Hope This Helps

[Domenic]

Try...

=SUMPRODUCT(--(A1>{0,200,500}),A1-{0,200,500},{0.38,-0.05,-0.05})

...where A1 contains your sales amount.

Hope this helps!

[vantage1]

Thank you all so much for your help. I don't think that I would have ever figured this one out. Thanks again, Daryl