Help with a formula

[richanor]

Could anybody help me out with a formula to work out some tiered pricing.

Assume that I have my pricing structure in cells A1:A5 as follows; A1=£250, A2=£200, A3=£150, A4=£100, A5=£50.

In cells B1:B5 I have the volume at which that price kicks in; B1=<50, B2=50-80, B3=80-100, B4=100-120, B5=>120

(so in this example, if a customer buys 90 units - the first 50 are £250, the next 30 are £200, and the last 10 are £150)

Is there a way that I can enter a number into a cell (D1) and it works out the total price?

Many thanks in advance

Rich

 

[DRSteele]

This [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]algorithm [/FONT]otta do it. Copy F2 downwards and enter your desired value in A9.

If you'd like more insight follow the links in this thread.
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]https://www.mrexcel.com/forum/excel-questions/1068302-calculating-commission-rates-excel-formula-question.html

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F
1				Brackets	Rate	Delta
2				0	250	250
3				50	200	-50
4				80	150	-50
5				100	100	-50
6				120	50	-50
7
8
9	90	20,000.00
CommBrackets (2)

Cell Formulas
Range	Formula
F2	=E2-N(E1)
B9	=SUMPRODUCT(--(A9>D2:D6),A9-D2:D6,F2:F6)

[/FONT]

[/FONT]

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	Rate	Brackets	Number	Total	Total
2						Row 2 is blank
3	250	0	90	20,000	20000
4	200	50
5	150	80
6	100	100
7	50	120
1c

Cell Formulas
Range	Formula
D3	=SUMPRODUCT(--(C3>rB),C3-rB,A3:A7-A2:A6)
E3	=SUMPRODUCT(--(C3>rB),C3-rB,{250;-50;-50;-50;-50})

Named Ranges
Name	Refers To	Cells
rB	='1c'!$B$3:$B$7