Pricing by volume tier

[BMil8]

I'm stumped on a formula. We have a tiered pricing model based on volume. I'm trying to create a table that breaks the YTD volume into the appropriate tier by month. Basically in the example below, the first 235,000 YTD are charged at a certain price, then 235,001-500,000 at a different price and so on. I'd just like a formula in the highlighted area to place the volume in the appropriate column. Did I give enough of an explanation?

Book9
	B	C	D	E	F	G	H	I
3				0	235,001	500,001	2,000,001
4	Month	YTD Links	New by Month	235,000	500,000	2,000,000	5,000,000	Total
5	Jan	217,569	217,569	217,569	0	0	0	217,569
6	Feb	237,565	19,996	17,431	2,565	0	0	19,996
7	Mar	248,963	11,398	0	11,398	0	0	11,398
8	Apr	304,856	55,893	0	55,893	0	0	55,893
9	May	385,000	80,144	0	80,144	0	0	80,144
10	Jun	499,521	114,521	0	114,521	0	0	114,521
11	Jul	608,976	109,455	0	109,455	0	0	109,455
12	Aug	757,939	148,963	0	126,024	22,939	0	148,963
13	Sep	1,459,653	701,714	0	0	701,714	0	701,714
14	Oct	3,059,785	1,600,132	0	0	1,275,347	324,785	1,600,132
15	Nov	4,148,964	1,089,179	0	0	0	1,089,179	1,089,179
16	Dec	6,598,654	2,449,690	0	0	0	2,449,690	2,449,690
17				235,000	500,000	2,000,000	3,863,654	6,598,654
Sheet1

Cell Formulas
Range	Formula
F3:H3	F3	=E4+1
C11	C11	=C10+109455
C12	C12	=C11+148963
D5	D5	=C5
D6:D16	D6	=C6-C5
I5:I17	I5	=SUM(E5:H5)
E17:H17	E17	=SUM(E5:E16)

 

[lrobbo314]

It would help if you would post an example of what your expected output would be.

The solution I gave matched what was in your original post.

But, I think that all you need to do is adjust the values in range E4:H4.

I got the total in column F to match what you said. I think I got Columns G and H to do what you want as well, but I'm not entirely sure.

Book1
	B	C	D	E	F	G	H	I
4	Month	YTD Links	New by Month	235000	264999	1499999	5000000	Total
5	Jan	217,569	217,569	217,569	0	0	0	217,569
6	Feb	237,565	19,996	17,431	2,565	0	0	19,996
7	Mar	248,963	11,398	0	11,398	0	0	11,398
8	Apr	304,856	55,893	0	55,893	0	0	55,893
9	May	385,000	80,144	0	80,144	0	0	80,144
10	Jun	499,521	114,521	0	114,521	0	0	114,521
11	Jul	608,976	109,455	0	478	108,977	0	109,455
12	Aug	757,939	148,963	0	0	148,963	0	148,963
13	Sep	1,459,653	701,714	0	0	701,714	0	701,714
14	Oct	3,059,785	1,600,132	0	0	540,345	1,059,787	1,600,132
15	Nov	4,148,964	1,089,179	0	0	0	1,089,179	1,089,179
16	Dec	6,598,654	2,449,690	0	0	0	2,449,690	2,449,690
17				235,000	264,999	1,499,999	4,598,656	6,598,654
Sheet2

Cell Formulas
Range	Formula
F4	F4	=(500000-E4)-1
G4	G4	=2000000-500000-1
C11	C11	=C10+109455
C12	C12	=C11+148963
D5	D5	=C5
D6:D16	D6	=C6-C5
I5:I17	I5	=SUM(E5:H5)
E17:H17	E17	=SUM(E5:E16)

I didn't change the VBA code at all.

 

[Dave Patton]

BMil8's information in posts #1 and #10 is probably incorrect.
The brackets don't usually have gaps.
A formula solution is shown in post #9. An arithmetic solution by tier was also in post #8.
Brackets are probably like

Commissions 2023.xlsm
	E	F	G	H
3		235000	500000	2000000
4	Bracket 1 0-235k	Bracket 2 235-500k	Bracket 3 500-2000k	Bracket 4 2000k & >
2hh

N.B. Previous post showed a Lambda version and formula solutions with the brackets and rates that I added so the calculation could be completed.
This post names the bracket information and the rate differential information. Some columns are hidden

Commissions 2023.xlsm
	B	C		E	F	G	H			K
1							Pricing cumulative sales
2
3					235000	500000	2000000
4	Month	YTD Links		Bracket 1 0-235k	Bracket 2 235-500k	Bracket 3 500-2000k	Bracket 4 2000k & >			Sum
5	Jan	217,569.00		217,569.00						10,878.45
6	Feb	237,565.00		17,431.00	2,565.00					948.50
7	Mar	248,963.00			11,398.00					341.94
8	Apr	304,856.00			55,893.00					1,676.79
9	May	385,000.00			80,144.00					2,404.32
10	Jun	499,521.00			114,521.00					3,435.63
11	Jul	608,976.00			479.00	108,976.00				2,193.89
12	Aug	757,939.00				148,963.00				2,979.26
13	Sep	1,459,653.00				701,714.00				14,034.28
14	Oct	3,059,785.00				540,347.00	1,059,785.00			21,404.79
15	Nov	4,148,964.00					1,089,179.00			10,891.79
16	Dec	6,598,654.00					2,449,690.00			24,496.90
17				235,000.00	265,000.00	1,500,000.00	4,598,654.00			95,686.54
18				5%	3%	2%	1%
19				11,750.00	7,950.00	30,000.00	45,986.54
2hh

Cell Formulas
Range	Formula
E5:H16	E5	=MAX(MAX(0,MIN($C5-E$3,F$3-E$3))-SUM(E$4:E4),0)
K5:K16	K5	=SUM((C5>rB)*(C5-rB)*rR)-SUM($J$4:J4)
E17:H17,K17	E17	=SUM(E5:E16)
E19:H19	E19	=E17*E18

 

[BMil8]

It would help if you would post an example of what your expected output would be.

The solution I gave matched what was in your original post.

But, I think that all you need to do is adjust the values in range E4:H4.

I got the total in column F to match what you said. I think I got Columns G and H to do what you want as well, but I'm not entirely sure.

Book1
	B	C	D	E	F	G	H	I
4	Month	YTD Links	New by Month	235000	264999	1499999	5000000	Total
5	Jan	217,569	217,569	217,569	0	0	0	217,569
6	Feb	237,565	19,996	17,431	2,565	0	0	19,996
7	Mar	248,963	11,398	0	11,398	0	0	11,398
8	Apr	304,856	55,893	0	55,893	0	0	55,893
9	May	385,000	80,144	0	80,144	0	0	80,144
10	Jun	499,521	114,521	0	114,521	0	0	114,521
11	Jul	608,976	109,455	0	478	108,977	0	109,455
12	Aug	757,939	148,963	0	0	148,963	0	148,963
13	Sep	1,459,653	701,714	0	0	701,714	0	701,714
14	Oct	3,059,785	1,600,132	0	0	540,345	1,059,787	1,600,132
15	Nov	4,148,964	1,089,179	0	0	0	1,089,179	1,089,179
16	Dec	6,598,654	2,449,690	0	0	0	2,449,690	2,449,690
17				235,000	264,999	1,499,999	4,598,656	6,598,654
Sheet2

Cell Formulas
Range	Formula
F4	F4	=(500000-E4)-1
G4	G4	=2000000-500000-1
C11	C11	=C10+109455
C12	C12	=C11+148963
D5	D5	=C5
D6:D16	D6	=C6-C5
I5:I17	I5	=SUM(E5:H5)
E17:H17	E17	=SUM(E5:E16)

I didn't change the VBA code at all.

Yes, you answered the question as I asked it. The error was on my side in how I submitted the table. Thanks for the follow up on just changing row 4. Much appreciated.