Commission Calculator based on total Amount of Sales

[IanShockey]

I need help with creating a formula for this commission structure.

The Initial formula I came up with is this.
=IF(K7>$K$4,(K7-K4)*$K$5,0)

I also need the calculation for anything above 100000 combined with this initial formula that I created so that if the amount exceeds 100000 it would not follow the 7% as it only applies to $40000-$99999
Anything above that will be at 9%.

Please help me solve this. ?

 

[Saba Sabaratnam]

You could use the following formula which is based on my spreadsheet layout.
=MAX(MIN(C7-C3,99999-C3),0)*C4+MAX(C7-D3,0)*D4

Kind regards

Saba

 

[IanShockey]

You could use the following formula which is based on my spreadsheet layout.
=MAX(MIN(C7-C3,99999-C3),0)*C4+MAX(C7-D3,0)*D4

View attachment 40952

Kind regards

Saba

Hi Saba,

I tried the min and max formula however it does not exclude the 7% on the calculation.
For example :
Total Deals Amount - $141,000
Commission Applied - 9%
Commission will only be applied to the 41,000 so the result should be
$3690.

It's like im trying to set a limit on the commission structure of 7%
That it should only be applied on a specific range which is $40000-$99999
Anything above $99999 the 9% should take effect.

 

[AhoyNC]

Maybe:

Book1
	J	K	L
4	Target	$ 40,000.00	$ 100,000.00
5	Rate	7%	9%
6
7	Total amount of Deals	$ 141,000.00
8	Monthly Commision	$ 3,690.00
Sheet1

Cell Formulas
Range	Formula
K8	K8	=IF(K7>99999,$L$5*($K$7-100000),IF($K$7>40000,$K$5*K7,0))

 

[Saba Sabaratnam]

Or you could use the following formula in C8

=IF(C7<D3,MAX(MIN(C7-C3,99999-C3),0)*C4,MAX(C7-D3,0)*D4)

Kind regards

Saba

 

[IanShockey]

Maybe:

Book1
	J	K	L
4	Target	$ 40,000.00	$ 100,000.00
5	Rate	7%	9%
6
7	Total amount of Deals	$ 141,000.00
8	Monthly Commision	$ 3,690.00
Sheet1

Cell Formulas
Range	Formula
K8	K8	=IF(K7>99999,$L$5*($K$7-100000),IF($K$7>40000,$K$5*K7,0))

Hey Ahoy,

We almost got it however when the total changes to something less than 100k
Its the same formula with the 40k
So if for example the total amount is 45000 the 5000 will be the one applied with the 7% Interest.

I tried changing the formula however it's an error when I tried to mimic the formula for 100k.

Thank you for helping.

 

[IanShockey]

Or you could use the following formula in C8

=IF(C7<D3,MAX(MIN(C7-C3,99999-C3),0)*C4,MAX(C7-D3,0)*D4)

View attachment 40956

Kind regards

Saba

I think we just got it right saba.
Let me test it.

 

[IanShockey]

I think we just got it right saba.
Let me test it.

Or you could use the following formula in C8

=IF(C7<D3,MAX(MIN(C7-C3,99999-C3),0)*C4,MAX(C7-D3,0)*D4)

View attachment 40956

Kind regards

Saba

Saba,

Can you try the 66000 amount to you bud?
What results does it show to you.
Shows me a total of 1,820

 

[Saba Sabaratnam]

Hi Ian,

Please see the result for two possibilities and reconciliation below.

Kind regards

Saba

 

[IanShockey]

Hi Ian,

Please see the result for two possibilities and reconciliation below.

View attachment 40957

View attachment 40958

Kind regards

Saba

We are almost there.. ?

The structure would be for $40,000
If the total is $40,000 less it would be 0.
If it would be $47,000 or any amount excess to $40,000 that's where the 7% is applied.
Total :47000
Commission: 7000*0.07

Thanks Saba ??