Sales commission

[chunu]

Hi,
Need formula to calculate sales commissions on incremental value.

Thanks

 

[offthelip]

Assuming the sales value is in A1 and the targert is in A3 then:

=A1*0.05 +MAX(A1-A3,0)*0.01+MAX(A1-1.2*A3,0)*0.02

 

[chunu]

Assuming the sales value is in A1 and the targert is in A3 then:

=A1*0.05 +MAX(A1-A3,0)*0.01+MAX(A1-1.2*A3,0)*0.02

Result should be same as my manual calculations

 

[offthelip]

it does give you the final result of 10600, do you need the internediate results as well.

 

[chunu]

it does give you the final result of 10600, do you need the internediate results as well.

Thanks got the desire result,
I appreciate if you tell me formula for intermediate result as well.

Thanks

 

[offthelip]

The intemediate results are:

[TABLE="width: 316"]
<colgroup><col></colgroup><tbody>[TR]
[TD]=MIN(A3,A1)*0.05[/TD]
[/TR]
[TR]
[TD]=MAX(MIN(A$1,1.2*A3)-A3,0)*0.06[/TD]
[/TR]
[TR]
[TD]=(MAX(A1,1.2*A3)-1.2*A3)*0.08[/TD]
[/TR]
</tbody>[/TABLE]

Same assumption as before Sales value in A1 sales target in A3

 

[chunu]

The intemediate results are:

[TABLE="width: 316"]
<tbody>[TR]
[TD]=MIN(A3,A1)*0.05[/TD]
[/TR]
[TR]
[TD]=MAX(MIN(A$1,1.2*A3)-A3,0)*0.06[/TD]
[/TR]
[TR]
[TD]=(MAX(A1,1.2*A3)-1.2*A3)*0.08[/TD]
[/TR]
</tbody>[/TABLE]

Same assumption as before Sales value in A1 sales target in A3

Excellent,

Thanks a lot