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?
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Lower Banding[/TD]
[TD]Higher Banding[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]5000[/TD]
[TD]£0.00[/TD]
[/TR]
[TR]
[TD]5001[/TD]
[TD]10000[/TD]
[TD]£3[/TD]
[/TR]
[TR]
[TD]10001[/TD]
[TD]20000[/TD]
[TD]£2.50[/TD]
[/TR]
[TR]
[TD]20001[/TD]
[TD]1000000[/TD]
[TD]£2.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[SyedUsman]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Lower Banding[/TD]
[TD]Higher Banding[/TD]
[TD]Price[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]5000[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5000[/TD]
[TD]10000[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]10000[/TD]
[TD]20000[/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]20000[/TD]
[TD]1000000[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]









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]

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Lower Banding
[/TD]
[TD]Higher Banding
[/TD]
[TD]Price
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]5000
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5000
[/TD]
[TD]10000
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]10000
[/TD]
[TD]20000
[/TD]
[TD]2.5
[/TD]
[/TR]
[TR]
[TD]20000
[/TD]
[TD]1000000
[/TD]
[TD]2
[/TD]
[/TR]
</tbody>[/TABLE]









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
No of units	Amount

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Banding[/TD]
[TD="align: center"]Price[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0 [/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"] 11,000 [/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"] 17,500 [/TD]

[TD="align: center"]3[/TD]
[TD="align: right"] 5,000 [/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"] 10,000 [/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"] 200,000 [/TD]
[TD="align: right"]-0.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

167​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=SUMPRODUCT((D2 > A2:A5) * (D2 - A2:A5) * B2:B5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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)