Tiered Pricing Structure; Array or Index Lookup

Hi and welcome to the forum,

Here's a example. The trick is to use the combination of INDEX and MATCH formulas.

In A1 to A5, enter the minimum qty of each tier:
0 (or 1) / 2 / 51 / 501 / 1001

In B1 to B5, enter the corresponding unit price:
33.792 / 33.178 / 33.666 / 32.052 / 31.539

In C1, enter the qty you want, exemple 17

In D1, enter this formula:

Please Login or Register  to view this content.

Maybe this can help
http://www.mcgimpsey.com/excel/variablerate.html

M.

Following the ideas presented on McGimpsey & Associates website (link in post #3) with small adjustments, try something like this

	A	B	C	D	E	F	G	H	I	J	K	L
1		From unit	To unit	Cost	Marginal Cost		Units	Total Cost			Units	Cost
2	Tier 1	1	1	33,792	33,792		52	1724,846		Tier 1	1	33,792
3	Tier 2	2	50	33,178	-0,614					Tier 2	49	1625,722
4	Tier 3	51	500	32,666	-0,512					Tier 3	2	65,332
5	Tier 4	501	1000	32,051	-0,615					Tier 4	0	0,000
6	Tier 5	1001		31,539	-0,512					Tier 5	0	0,000
7										Total	52	1724,846
8

Formula in E2
=D2

Formula in E3 copied down till E6
=D3-D2

Put the quantity of units you need in G2 and to calculate the cost insert this formula in H2
=SUMPRODUCT(--(G2>=$B$2:$B$6),1+G2-$B$2:$B$6,$E$2:$E$6)

Remark: the gray area, columns J to L, shows another way to achieve the same goal and also has checking purposes.
Formula in K2 copied down till K6
=IF(C2="",$G$2-SUM(K$1:K1),MIN($G$2-SUM(K$1:K1),C2-N(C1)))

Formula in K7
=SUM(K2:K6)

Formula in L2 copied down till L6
=K2*D2

Formula in L7
=SUM(L2:L6)

Hope this helps

M.

here's a simpler formula. If your ordered quantity is in cell A2:

=SUMPRODUCT(--(A2>{0,1,50,500,1000}),--(A2-{0,1,50,500,1000}),{33.792,-0.614,-0.512,-0.615,-0.512})

Originally Posted by beermn540

Thanks MLCB! Works perfectly, much thanks!!!

You are welcome. Glad to help

M.