Tiered Pricing Structure; Array or Index Lookup

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.

Originally Posted by beermn540

Thanks MLCB! Works perfectly, much thanks!!!

You are welcome. Glad to help

M.