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)

 

[Dave Patton]

Why don't you calculate the price based on the cumulative sales?
There are many versions of tiered pricing on this forum.
If I provide a suggestion, will you review it?

 

[BMil8]

Why don't you calculate the price based on the cumulative sales?
There are many versions of tiered pricing on this forum.
If I provide a suggestion, will you review it?

Thanks, but I'm not sure I understand your question. We bill each month based on new volume, using the tier each unit would fall in based on the YTD number. Meaning if there were 20K sold in November, and 600K sold from Jan-Nov, we would create an invoice for the 20K units, but using the price associated with the 600K. Regardless, I'm just looking for a formula to replicate what I have manually entered in the highlighted section above.

 

[Dave Patton]

We bill each month based on new volume, using the tier each unit would fall in based on the YTD number.

OK
I just thought you may want to do the calculation with one formula.

 

[lrobbo314]

You can do it with the following VBA code.

EXCEL
	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:I16	I5	=SUM(K5:N5)
E17:H17	E17	=SUM(K5:K16)
I17	I17	=SUM(E17:H17)

Sub Tier()
Dim r As Range:         Set r = Range("E5:H16")
Dim AR() As Variant:    AR = Range("D5:D16").Value2
Dim RES() As Variant:   RES = Evaluate(Replace("=IF(ISERROR(@),0,0)", "@", r.Address))
Dim Tiers As Variant:   Tiers = Range("E4:H4")
Dim COL As Integer:     COL = 1
Dim Total As Long:      Total = 0


For i = 1 To UBound(AR)
    Total = Total + AR(i, 1)
    If Total > Tiers(1, COL) Then
        RES(i, COL + 1) = Total - Tiers(1, COL)
        RES(i, COL) = AR(i, 1) - RES(i, COL + 1)
        Total = RES(i, COL + 1)
        COL = COL + 1
    Else
        RES(i, COL) = AR(i, 1)
    End If
Next i

r.Value = RES
End Sub

 

[BMil8]

You can do it with the following VBA code.

EXCEL
	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:I16	I5	=SUM(K5:N5)
E17:H17	E17	=SUM(K5:K16)
I17	I17	=SUM(E17:H17)

Sub Tier()
Dim r As Range:         Set r = Range("E5:H16")
Dim AR() As Variant:    AR = Range("D5:D16").Value2
Dim RES() As Variant:   RES = Evaluate(Replace("=IF(ISERROR(@),0,0)", "@", r.Address))
Dim Tiers As Variant:   Tiers = Range("E4:H4")
Dim COL As Integer:     COL = 1
Dim Total As Long:      Total = 0


For i = 1 To UBound(AR)
    Total = Total + AR(i, 1)
    If Total > Tiers(1, COL) Then
        RES(i, COL + 1) = Total - Tiers(1, COL)
        RES(i, COL) = AR(i, 1) - RES(i, COL + 1)
        Total = RES(i, COL + 1)
        COL = COL + 1
    Else
        RES(i, COL) = AR(i, 1)
    End If
Next i

r.Value = RES
End Sub

This works great thanks. I didn't even realize you could use VBA for things like this.

 

[Dave Patton]

N.B. You did not provide the rates for each tier. The rates are shown in the next post.
This example does not require a macro enable file.

Commissions 2023.xlsm
	A	B	C
1			Version a
2		Sales	Price
3	January	217,569.00	10,878.45
4	February	237,565.00	948.50
5	March	248,963.00	341.94
6	April	304,856.00	1,676.79
7	May	385,000.00	2,404.32
8	June	499,521.00	3,435.63
9	July	608,976.00	2,193.89
10	August	757,939.00	2,979.26
11	September	1,459,653.00	14,034.28
12	October	3,059,785.00	21,404.79
13	November	4,148,964.00	10,891.79
14	December	6,598,654.00	24,496.90
15			95,686.54
2h

Cell Formulas
Range	Formula
B9	B9	=B8+109455
B10	B10	=B9+148963
C3:C14	C3	=Pricing_a(B3)-SUM($C$2:C2)
C15	C15	=SUM(C3:C14)

Lambda Functions
Name	Formula
Pricing_a	=LAMBDA(CumRev,LET(b, {0;235000;500000;2000000}, r, {0.05;-0.02;-0.01;-0.01}, SUM((CumRev > b) * (CumRev - b) * r)))

 

[Dave Patton]

Commissions 2023.xlsm
	A	B	C		E	F
1			Version a		Version b	Cumulative
2		Sales	Price		Price	Price
3	January	217,569.00	10,878.45		10,878.45	10,878.45
4	February	237,565.00	948.50		948.50	11,826.95
5	March	248,963.00	341.94		341.94	12,168.89
6	April	304,856.00	1,676.79		1,676.79	13,845.68
7	May	385,000.00	2,404.32		2,404.32	16,250.00
8	June	499,521.00	3,435.63		3,435.63	19,685.63
9	July	608,976.00	2,193.89		2,193.89	21,879.52
10	August	757,939.00	2,979.26		2,979.26	24,858.78
11	September	1,459,653.00	14,034.28		14,034.28	38,893.06
12	October	3,059,785.00	21,404.79		21,404.79	60,297.85
13	November	4,148,964.00	10,891.79		10,891.79	71,189.64
14	December	6,598,654.00	24,496.90		24,496.90	95,686.54
15			95,686.54		95,686.54
16
17	Total	6,598,654.00	95,686.54		95,686.54
18
19
20	Brackets	Rates	Tier		Arithmetic Cumulative or	Arithmetic
21
22	0	5.00%	235,000.00		11,750.00	11,750.00
23	235,000	3.00%	265,000.00		19,700.00	7,950.00
24	500,000	2.00%	1,500,000.00		49,700.00	30,000.00
25	2,000,000	1.00%	4,598,654.00		95,686.54	45,986.54
26	1.E+308	0.50%
27
28	Amount	6,598,654.00			95,686.54	95,686.54
29
2h

Cell Formulas
Range	Formula
B9	B9	=B8+109455
B10	B10	=B9+148963
F3:F14	F3	=Pricing_a(SUM(B3))
C3:C14	C3	=Pricing_a(B3)-SUM($C$2:C2)
C15,E15	C15	=SUM(C3:C14)
E3:E14	E3	=LET(s,B3,b,{0;235000;500000;2000000},r,{0.05;-0.02;-0.01;-0.01},SUM((s>b)*(s-b)*r)-SUM($E$2:E2))
B17	B17	=B14
C17	C17	=Pricing_a(B17)
E17	E17	=SUM((B17>{0;235000;500000;2000000})*(B17-{0;235000;500000;2000000})*{0.05;-0.02;-0.01;-0.01})
E22:E25	E22	=MAX(0,MIN($B$28-A22,A23-A22))*B22+E21
F22:F25	F22	=MAX(0,MIN($B$28-A22,A23-A22)*B22)
C22:C25	C22	=MAX(0,MIN($B$28-A22,A23-A22))
A26	A26	=BigNum
B28	B28	=B17
E28	E28	=MAX(E22:E25)
F28	F28	=SUM(F22:F26)

Lambda Functions
Name	Formula
Pricing_a	=LAMBDA(CumRev,LET(b, {0;235000;500000;2000000}, r, {0.05;-0.02;-0.01;-0.01}, SUM((CumRev > b) * (CumRev - b) * r)))

 

[Dave Patton]

or

Commissions 2023.xlsm
	B	C	D	E	F	G	H	I	J
1							info
2
3				0	235000	500000	2000000	1E+308
4	Month	YTD Links	New by Month					Total
5	Jan	217569	217569	217569	0	0	0	217569	10,878.45
6	Feb	237565	19996	17431	2565	0	0	19996	948.50
7	Mar	248963	11398	0	11398	0	0	11398	341.94
8	Apr	304856	55893	0	55893	0	0	55893	1,676.79
9	May	385000	80144	0	80144	0	0	80144	2,404.32
10	Jun	499521	114521	0	114521	0	0	114521	3,435.63
11	Jul	608976	109455	0	479	108976	0	109455	2,193.89
12	Aug	757939	148963	0	0	148963	0	148963	2,979.26
13	Sep	1459653	701714	0	0	701714	0	701714	14,034.28
14	Oct	3059785	1600132	0	0	540347	1059785	1600132	21,404.79
15	Nov	4148964	1089179	0	0	0	1089179	1089179	10,891.79
16	Dec	6598654	2449690	0	0	0	2449690	2449690	24,496.90
17				235000	265000	1500000	4598654	6598654
18				5%	3%	2%	1%
19				11,750.00	7,950.00	30,000.00	45,986.54	95,686.54	95,686.54
20
2hh

Cell Formulas
Range	Formula
I3	I3	=BigNum
D5	D5	=C5
E5:H16	E5	=MAX(MAX(0,MIN($C5-E$3,F$3-E$3))-SUM(E$4:E4),0)
I19,I5:I17	I5	=SUM(E5:H5)
J5:J16	J5	=Pricing_a(C5)-SUM($J$4:J4)
D6:D16	D6	=C6-C5
C11	C11	=C10+109455
C12	C12	=C11+148963
E17:H17	E17	=SUM(E5:E16)
E19:H19	E19	=E17*E18
J19	J19	=SUM(J5:J16)

 

[BMil8]

You can do it with the following VBA code.

EXCEL
	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:I16	I5	=SUM(K5:N5)
E17:H17	E17	=SUM(K5:K16)
I17	I17	=SUM(E17:H17)

Sub Tier()
Dim r As Range:         Set r = Range("E5:H16")
Dim AR() As Variant:    AR = Range("D5:D16").Value2
Dim RES() As Variant:   RES = Evaluate(Replace("=IF(ISERROR(@),0,0)", "@", r.Address))
Dim Tiers As Variant:   Tiers = Range("E4:H4")
Dim COL As Integer:     COL = 1
Dim Total As Long:      Total = 0


For i = 1 To UBound(AR)
    Total = Total + AR(i, 1)
    If Total > Tiers(1, COL) Then
        RES(i, COL + 1) = Total - Tiers(1, COL)
        RES(i, COL) = AR(i, 1) - RES(i, COL + 1)
        Total = RES(i, COL + 1)
        COL = COL + 1
    Else
        RES(i, COL) = AR(i, 1)
    End If
Next i

r.Value = RES
End Sub

I just realized this doesn't work exactly as needed. You can see in column F the total is the entire 500,000 when it should just include (500,000-235,001) = 264,999 that fall within that range. What do I need to adjust?