Calculating tiered payout with cumulative numbers

[kshankar88]

Hello,
I took a look at the tiered payout problems posted but I could not apply same solution as my problems differs as below:
[TABLE="width: 141"]
<tbody>[TR]
[TD="width: 107, align: center"]Annual New client (no: of clients)[/TD]
[TD="width: 81, align: center"]Payout in $ per client[/TD]
[/TR]
[TR]
[TD="align: center"]0-40[/TD]
[TD="align: center"]50[/TD]
[/TR]
[TR]
[TD="align: center"]41-60[/TD]
[TD="align: center"]100[/TD]
[/TR]
[TR]
[TD="align: center"]61-90[/TD]
[TD="align: center"]200[/TD]
[/TR]
[TR]
[TD="align: center"]91+[/TD]
[TD="align: center"]250[/TD]
[/TR]
</tbody>[/TABLE]


In my problem, I have something called marginal pay in cumulative fashion, like how incentive/bounty gets paid. For e.g., I have the first 40 clients paid $50 each, the next 20 paid $100 each and the next 30 are paid $200 each, etc. so, when I have 90 new clients, the payout is $10,000 (NOT $18,000).


I tried to calculate the payout at each tier and different payout, but did not go right with sumproduct logic. Could you pl help what could be solution suggested?

Thanks,
Shankar

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H
1	Name	Jan	Feb	Mar	Apr			Tiered Commission
2	Client1	24	35	14	29
3	Monthly Payout	1,200.00	2,700.00	2,700.00	6,400.00
4
5		Units	Amount		Number of clients	Payout	r_Delta
6	Total	102	13,000.00		0	50	50
7					40	100	50
8					60	200	100
9					90	250	50
1aa

B3 =SUMPRODUCT(--(SUM($A$2:B2)>r_Tiers),SUM($A$2:B2)-r_Tiers,r_Delta)-SUM($A$3:A3)
C6 =SUMPRODUCT(--(B6>r_Tiers),B6-r_Tiers,r_Delta)
G6 =F6-N(F5)

 

[kshankar88]

SUM($A$2:B2)

Will sum the text in A2 and B2 which will evaluate as 0 + value in B2

SUM($A$3:A3) at the end of the formula sums the cells to the left.
The cell A3 has text;consequently, this part of the formula correctly subtracts 0 when the formula is in B3.

This structure lets one copy the same formula across to calculate the result for each column.

Thank you very much Dave and shg. It was very useful.