Formula For Price Breaks on Volume

Hey so I need a formula that gives price breaks as the amount input increases. So there's a box you input and amount. This amount correlates with a price. So if i'ts between 0-200 its 6$ it its 201-500 its 5$ then if its between 501-1000 it's 4$. Can someone help me with this. Thanks!!

Hi,

Two ways:

You can "hard-code" the levels in the formula as in my sample C2.

Or, use a Table as in my formula in my sample B2, using E2:G4 as the Table, when the levels changes in the future, just change the values in the Table without needing to update the formulas.

Assumes anything 501 and over is $4 (including over 1000).

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G
1	Amount	520	520		Volumne		Price
2	Price	4	4		0	200	6
3					201	500	5
4					501	1000	4

Sheet: Sheet33

Excel 2016 (Windows) 64 bit

	B	C
2	=LOOKUP(B1,$E$2:$E$4,$G$2:$G$4)	=LOOKUP(C1,{0,201,501},{6,5,4})

Sheet: Sheet33