Cumulative tiered commission & exclude big deals from cumulative calc

[CommAnalyst]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Tier[/TD]
[TD]Rate[/TD]
[TD]Differential Rate[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]0[/TD]
[TD]5%[/TD]
[TD]5%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]500,001[/TD]
[TD]10%[/TD]
[TD]5%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]100,0001[/TD]
[TD]15%[/TD]
[TD]5%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1,500,001[/TD]
[TD]20%[/TD]
[TD]5%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Deal 1 Amount[/TD]
[TD]1,000,000[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Commission[/TD]
[TD]=sumproduct((A1:A4<B6)*(B5-A1:A4)*(C1:C4))[/TD]
[TD]=75,000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I have a tiered comm calculation using the above formula for the 1st deal the person completes.
Question/Problem: How do I calculate the subsequent comm, if the rate is based on the accumulation of previous deals tiering.
For example Deal 2 with a value of 300,000 will earn 15%. So its, comm should be 300,000*15%=45,000
In addition a deal with a value of 1,500,001 are excluded from the accumulation level and use the normal tiers

 

[Aladin Akyurek]

See if this link helps:

https://www.mrexcel.com/forum/excel-questions/357834-tiered-commission-formula.html