Commision Formula

[Welsh Mark3]

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

o 0-2000,0%
o 2001-4000,15% (retroactive to dollar 1)
o 4001-6000,30% (30% applies to 4001-6000)
o 6001+,45% (45% applies to everything above 6001)


Please let me know if you can help.

 

[AlanY]

try this

Excel 2013/2016
	A	B	C	D	E
1	sales	commission		sales	commission
2	5,000	600		0	0%
3				2,001	15%
4				4,001	15%
5				6,001	15%
167

Cell Formulas
Range	Formula
B2	=SUMPRODUCT(--(A2>D2:D5),A2-D2:D5,E2:E5)

 

[Welsh Mark3]

I think this formula is flawed somewhere. For example if $4000 the result should be $600, this formula returns $299.85

 

[AlanY]

ok, i missed the (retroactive to dollar 1) bit,

try the amended formula

=SUMPRODUCT(--(A2>D2:D5),A2-D2:D5,E2:E5)+IF(A2>2000,2000*0.15,0)