Summing cost of goods based on price and quantity thresholds

[gwstat001]

Tables below refer. Is there an easy way to calculate the price of order quantities each month based on the threshold table below? Basically the threshold table says if order quantity is less than 5M, each item will be cost $0.30. If order quantity is more than 5M but less than 7.5M, the first 5M will be charged at $0.30, then the rest above 5M will be charged at $0.27. And so on until order quantity above 20M.

Threshold	Price per unit
0	$ 0.30
5 000 000	$ 0.27
7 500 000	$ 0.25
10 000 000	$ 0.24
15 000 000	$ 0.22
20 000 000	$ 0.20

Formula needed for each month in 3rd column (Price) below:

Month	Quantity	Price
1	8 802 994	?
2	6 378 962	?
3	8 398 221	?
4	9 061 017	?
5	4 988 435	?
6	9 883 952	?
7	4 544 655	?
8	5 818 679	?
9	5 052 615	?
10	8 316 649	?
11	7 926 167	?
12	7 197 486	?

 

[KOKOSEK]

Check is it correct prices calculated:

Excel 2016 (Windows) 32 bit

A​	B​	C​
Month	Quantity	Price
1​	8,802,994​	2,500,749​
2​	6,378,962​	1,872,320​
3​	8,398,221​	2,399,555​
4​	9,061,017​	2,565,254​
5​	4,988,435​	1,500,000​
6​	9,883,952​	2,770,988​
7​	4,544,655​	1,500,000​
8​	5,818,679​	1,721,043​
9​	5,052,615​	1,514,206​
10​	8,316,649​	2,379,162​
11​	7,926,167​	2,281,542​
12​	7,197,486​	2,093,321​

 

[KOKOSEK]

Above seems to be correct, if I catch calculation method correctly.

Here the formulas:

Table a bit recreated:

A​	B​	C​
5,000,000.00​	0.3​	=(A1*B1)​
7,500,000.00​	0.27​	=((A2-A1)*B2)+SUM($C$1:C1)​
10,000,000.00​	0.25​	=((A3-A2)*B3)+SUM($C$1:C2)​
15,000,000.00​	0.24​	=((A4-A3)*B4)+SUM($C$1:C3)​
20,000,000.00​	0.22​	=((A5-A4)*B5)+SUM($C$1:C4)​
10,000,000,000.00​	0.2​

Drag down formula in col. C for the rest of months:

	A​	B​	C​
8​	Month	Quantity	Price
9​	1​	8,802,994​	=IFERROR(VLOOKUP(B9,$A$1:$C$6,3,1)+(B9-INDEX($A$1:$A$6,MATCH(B9,$A$1:$A$6,1)))*INDEX($B$1:$B$6,(MATCH(B9,$A$1:$A$6,1)+1)),MIN($C$1:$C$6))​

 

[Sanjeev1976]

One more solution using only Vlookup

Book6
	A	B	C
1	Threshold	Price per unit	Helper Column
2	-	$0.30
3	5,000,000	$0.27	1,500,000
4	7,500,000	$0.25	2,175,000
5	10,000,000	$0.24	4,300,000
6	15,000,000	$0.22	9,175,000
7	20,000,000	$0.20	18,250,000
8
9	Month	Quantity	Price
10	1	8,802,994	2,500,749
11	2	6,378,962	1,872,320
12	3	8,398,221	2,399,555
13	4	9,061,017	2,565,254
14	5	4,988,435	1,496,531
15	6	9,883,952	2,770,988
16	7	4,544,655	1,363,397
17	8	5,818,679	1,721,043
18	9	5,052,615	1,514,206
19	10	8,316,649	2,379,162
20	11	7,926,167	2,281,542
21	12	7,197,486	2,093,321
Sheet1

Cell Formulas
Range	Formula
C3	C3	=(A3-A2)*B2
C4:C7	C4	=(A4-A3)*B3+SUM($C$3:C3)
C10:C21	C10	=(B10-VLOOKUP(B10,$A$2:$C$7,1))*VLOOKUP(B10,$A$2:$C$7,2)+VLOOKUP(B10,$A$2:$C$7,3)

 

[gwstat001]

Worked like a charm. Thank you!