Scaled Tax/Commission etc

[iansaunderson]

Hi guys

I have a various level pricing format as below, which should work similar to scaled commission / tax calculations.

For example, 15000 units, should be charged as the first 5k (£0), the next 5k (£3 per unit) and the remaining 5k units charged at £2.50 per unit. I've done some research on SUMPRODUCT but couldn't get the formula to work.

Any chance of some help?

Lower Banding	Higher Banding	Price
0	5000	£0.00
5001	10000	£3
10001	20000	£2.50
20001	1000000	£2.00

<tbody>
</tbody>

 

[SyedUsman]

Lower Banding	Higher Banding	Price
0	5000	0
5000	10000	3
10000	20000	2.5
20000	1000000	2

<tbody>
</tbody>

Modified the table a bit

=SUMPRODUCT(IF(E1>A2:A5,B2:B5)-IF(E1>A2:A5,A2:A5),IF(E1>A2:A5,C2:C5))-(((IF(SUM(--(E1=A2:A5))=1,E1,LOOKUP(E1,A2:B5,B2:B5))))-E1)*LOOKUP(E1,A2:B5,C2:C5)

Assuming Table with headers A1:C5 & units are in cell E1

 

[AlanY]

try this

Excel 2012
	A	B	C	D	E
1	Banding	Price		No of units	Amount
2	0	0		11,000	17,500
3	5,000	3
4	10,000	-0.5
5	200,000	-0.5
167

Cell Formulas
Range	Formula
E2	=SUMPRODUCT((D2 > A2:A5) * (D2 - A2:A5) * B2:B5)

 

[AlanY]

to clarify, B4 = 2.5-3 and B5=2-2.5
both ended up with -0.5

try this

Excel 2012
	A	B	C	D	E
1	Banding	Price		No of units	Amount
2	0	0		11,000	17,500
3	5,000	3
4	10,000	-0.5
5	200,000	-0.5
167

Cell Formulas
Range	Formula
E2	=SUMPRODUCT((D2 > A2:A5) * (D2 - A2:A5) * B2:B5)

 

[iansaunderson]

Lower Banding	Higher Banding	Price
	5000
5000	10000	3
10000	20000	2.5
20000	1000000	2

<tbody>
</tbody>

Modified the table a bit

=SUMPRODUCT(IF(E1>A2:A5,B2:B5)-IF(E1>A2:A5,A2:A5),IF(E1>A2:A5,C2:C5))-(((IF(SUM(--(E1=A2:A5))=1,E1,LOOKUP(E1,A2:B5,B2:B5))))-E1)*LOOKUP(E1,A2:B5,C2:C5)

Assuming Table with headers A1:C5 & units are in cell E1

Thanks for the reply, but the answer appears to be way over the supposed answer?

 

[SyedUsman]

Forgot to mention
use with ctrl + shift + enter

 

[iansaunderson]

try this

Excel 2012

	A	B	C	D	E
1	Banding	Price		No of units	Amount
2	0	0		11,000	17,500
3	5,000	3
4	10,000	-0.5
5	200,000	-0.5

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

167​

Worksheet Formulas Cell Formula E2 =SUMPRODUCT((D2 > A2:A5) * (D2 - A2:A5) * B2:B5) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Thanks, but I'm getting #VALUE ! return?

 

[SyedUsman]

Thanks, but I'm getting #VALUE ! return?

Remove the column/Row labels then apply the formula

 

[AlanY]

Thanks, but I'm getting #VALUE ! return?

are your prices in Col B or C, may need to adjust formula to suit?

 

[iansaunderson]

are your prices in Col B or C, may need to adjust formula to suit?

Units = P41
Lower Bandings = W46 - W54
Higher Bandings = X46 - X54
Charges = Z46 - Z54
Differences = AA46 - AA54

=SUMPRODUCT((P41 > X46:X54) * (P41 - X46:X54) * AA46:AA54)