Commision Calculator

[Welsh Mark3]

I have posted a similar thread earlier but the formaula I am looking for is now different. I'm looking to create a formula that calculates sales commission from monthly sales achieved. Cell A1 will contain monthly sales achieved.

The commission structure is

$0-$14,999 - 10%
15k-18k - 20% (20% on all of 15k once hit)
18k+ 30% ( this only applies to the value above 18K)


Please let me know if you can help.

 

[lrobbo314]

Let me know if this does what you are looking for.

=A1*(1+(LOOKUP(A1,{0,15000,18000},{0.1,0.2,0.3})))

 

[Welsh Mark3]

I think the formula is flawed as the calculation brings me back a result of 5338 when sales are 4899

 

[lrobbo314]

At $4,899, they should betting 10% commission right? 10% of $4,899 is $489.9. That added back to the original of 4,899 is $5,388.9. Are you looking for just the commission part?

=A1*LOOKUP(A1,{0,15000,18000},{0.1,0.2,0.3})

This would return 489.9 from 4,899

 

[MARK858]

The commission structure is

$0-$14,999 - 10%
15k-18k - 20% (20% on all of 15k once hit)
18k+ 30% ( this only applies to the value above 18K)

Possibly...

Excel 2010
	A	B	J
1	19000		3900
Sheet1

Cell Formulas
Range	Formula
J1	=MIN(A1,18000)*LOOKUP(MIN(A1,18000),{0,15000},{0.1,0.2})+IF(A1>18000,(A1-18000)*0.3,0)

 

[meytithveasna]

Try this
=A1*LOOKUP(A1,{0,0.1;15000,0.2;18001,0.3}

 

[MARK858]

Try this
=A1*LOOKUP(A1,{0,0.1;15000,0.2;18001,0.3}

I don't think you are taking the conditions in the brackets into account, particularly for 18k+

The commission structure is

$0-$14,999 - 10%
15k-18k - 20% (20% on all of 15k once hit)
18k+ 30% ( this only applies to the value above 18K)

so for 19000 you would get 18000 * 0.2 = 3600 + 1000 * 0.3 = 300 giving a total of 3900.