Working out commission structure

[getafix40]

Column AB is the gross profit entry.

The sales agent ears commission as follows:

0-19999 @ 10%
20000 upwards is 20%

The difficult part is that the 20% is payable from 20000 GP and not calculated from 0.

EG. If total GP is 15000$ the agent will earn 1500$
If total GP is 45000$ the agent will earn 5000$ (45000-20000=25000*20%)

Total comm will be 2000$ + 5000$=7000$

AB=Gross Profit
AD=Commission @ 10% less than 20000 GP
AE=Commission @ 20% in excess of 20000 GP
AF=Total Commission

Formula for AD is =IF(AB90<19999,AB90*10%)

Not sure how to work out the rest

 

[NeonRedSharpie]

You actually don't need any helper columns unless you just want them:

=IF($AB1<20000,10%*$AB1,(10%*19999)+(20%*($AB1-19999)))

This will say:

If it's less than 20,000 (eg. 0-19,999), the value is 10% of the value in AB. Otherwise, it's (10% * 19,999) + (20% * the value LESS 19,999).

If you want the helper column:

=IF($AB1>19999,20%*$AB1,0)

 

[shg]

Another way:

	A​	B​	C​
1​	Sales​	Comm​	​
2​	$ 5,000​	$ 500​	B2: =10%*A2 + MAX(0, 10%*(A2-10000))
3​	$ 10,000​	$ 1,000​
4​	$ 15,000​	$ 2,000​
5​	$ 20,000​	$ 3,000​
6​	$ 25,000​	$ 4,000​

 

[getafix40]

You actually don't need any helper columns unless you just want them:

=IF($AB1<20000,10%*$AB1,(10%*19999)+(20%*($AB1-19999)))

This will say:

If it's less than 20,000 (eg. 0-19,999), the value is 10% of the value in AB. Otherwise, it's (10% * 19,999) + (20% * the value LESS 19,999).

If you want the helper column:

=IF($AB1>19999,20%*$AB1,0)

Thanks a stack - you're a star.

I have included the helper columns as m boss likes to see exactly how things work out.

Your 1st code is what I was looking for, the second I had already worked out.

Thanks a mill