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]

 

[AlanY]

it works with this, note the ranges are difference

Excel 2012
	P	Q	V	W	Y	Z	AA
40	No of units	Amount
41	11,000	17,500
42
43
44
45				Banding			Price
46				0		0	0
47				5,000		3	3
48				10,000		2.5	-0.5
49				20,000		2	-0.5
Sheet1

Cell Formulas
Range	Formula
Q41	=SUMPRODUCT((P41 > W46:W49) * (P41 - W46:W49) * AA46:AA49)