SUMPRODUCT on two sets of ranges to calculate accumulated sum values

[Capolavoro009]

Apologies for garbled image below but what I’m trying to do is to determine which range my value falls between 0-60,703,125 and so on and then return corresponding commission value on the table range on the right hand side between 0-607,031

In addition as my value falls in different tiers (slices) I want to apply the corresponding commission for preceding steps plus the delta step.

For example:
If my stock value is 80,000,000 then the first 60,703,125 should be 607031 while 80million-60,7 million should be falling in the slice between 607031 and 1214062.

Thank you in advance for the help

Stock Value Tiers	Rev Share %age	Paid Commissions				60703125
	10%	R 0.00	R 60,703,125.00	10%	0	R 607,031.25
	15%	R 60,703,125.00	R 91,054,687.50	20%	R 607,031.25	R 1,214,062.50
	20%	R 91,054,687.50	R 121,406,250.00	30%	R 1,214,062.50	R 1,821,093.75
	60%	R 121,406,250.00	R 364,218,750.00	40%	R 1,821,093.75	R 2,428,125.00
	100%	R 364,218,750.00	R 607,031,250.00	60%	R 2,428,125.00	R 3,642,187.50

 

[Dave Patton]

I recommend one of the formula suggestions.

The example below shows a ways to calculate the commission at each bracket level. If you want even more detail,
you can separate the calculation into columns.

Commission Cumulative Sales.xlsm
	A	B	C
1
2	400,000,000	139,839,843.70	139,839,843.70
3
4	Tier Minimum	Payout Rate
5
6	0	10%	6,070,312.50
7	60,703,125	20%	6,070,312.60
8	91,054,688	30%	9,105,468.60
9	121,406,250	40%	97,125,000.00
10	364,218,750	60%	21,468,750.00
11	1.E+308
1e

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--(A2>aB),(A2-aB),aR)
C2	C2	=SUM(C6:C10)
C6:C10	C6	=MAX(0,MIN($A$2-A6,A7-A6))*B6
A11	A11	=9.99999999999999E+307

 

[Capolavoro009]

Thank you but i again think that the %age values are confusing here as there is no connection of them in the commission caculation formulas:

So the following table shows the input values as far as stock Tiers is concerned and what commissions should be paid out

Nedbank Littlefish Business Modeling_RevA.xlsx
	B	C	D	E
3	Stock Value Tiers		Paid Commissions
4	ZAR -	ZAR 60,703,125	-	607,031
5	ZAR 60,703,125	ZAR 91,054,688	ZAR 607,031	ZAR 1,214,063
6	ZAR 91,054,688	ZAR 121,406,250	ZAR 1,214,063	ZAR 1,821,094
7	ZAR 121,406,250	ZAR 364,218,750	ZAR 1,821,094	ZAR 2,428,125
8	ZAR 364,218,750	ZAR 607,031,250	ZAR 2,428,125	ZAR 3,642,188
Sheet1

Cell Formulas
Range	Formula
B5:B8,D5:D8	B5	=C4

 

[Dave Patton]

A clear example complete with expected results would be helpful.
A formula can provide the answers that you require with or without the supporting table.
Your Paid Commissions show some 'unusual' results but I used that information to derive the rates.

Commission Cumulative Sales.xlsm
	A	B	C
1
2	80,000,000	992,968.50
3
4	Tier Minimum	Payout Rate
5
6	0	1.00%
7	60,703,125	2.00%
8	91,054,688	2.00%
9	121,406,250	0.25%
10	364,218,750	0.50%
11
12
13	80,000,000	992,968.50
14	60,703,125	607,031.00
15	91,054,688	1,214,062.26
16	303,515,625	2,276,366.94
17	400,000,000	2,607,031.00
18	607,031,250	3,642,187.25
19	364,218,750	2,428,124.75
20
1e

Cell Formulas
Range	Formula
B2,B13:B19	B2	=SUM((A2>$A$6:$A$10)*(A2-$A$6:$A$10)*($B$6:$B$10-$B$5:$B$9))