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>

 

[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)