Volume Based Discounting - Total Cost Formula Question

[VTHokie11]

Good evening and happy Friday Team,

I've been struggling to come up with a good solution to a volume based discounting calculator I'm trying to build. I need a formula in Cell C3, which calculates the total cost. This formula needs to apply a certain rate against the first 5 Million units; a different rate against the Next 20 Million Units; a different rate against the Next 75 Million Units; so on and so forth. Eventually above 1 Billion Units they will be charged a consistent rate (but all of their usage up until that point will still incur the associated rates of the prior rate tiers).

Unfortunately rate is made up here, no early retirement for me sadly . Would greatly appreciate if someone could help me with the logic and excel formula to plug into Cell C3 here.

Please let me know if you have any questions or if this ask is unclear. Many thanks in advance and have a great weekend team!

 

[Michael M]

Maybe this

=IF(C2<5000001,C2*E6,IF(C2<25000001,C2*E7,IF(C2<100000001,C2*E8,IF(C2<250000001,C2*E9,IF(C2<500000001,C2*E10,IF(C2<1000000001,C2*E11,C2*E12))))))

 

[Dave Patton]

There are many examples on the forum for Tiered Commissions etc.
The formulas in C2:E2 use named arrays. The array of Brackets data and the array of Rate Differentials are named.
See Formulas Name Manger
- The array of Bracket data is named aB Refers to {0;5000000;25000000;100000000;250000000;500000000;1000000000}
- The array of Rate Differentials is named aR Refers to ={7;-1;-1;-1;-1;-1;-1}
The formula is easier to read.
The table is not required if the formulas in C2:E2 are used.
N.B.
The names are not necessary; it is a personal preference.
The names can be assigned to be applicable to the sheet or to the workbook.
We can determine the numbers by looking at the tier specifications or by reviewing the ranges.
For example with the formula in B2, select B8:B14-B7:B13 and press F9.

Commission2022.xlsm
	A	B	C	D	E
1	Number	10,000,000.00
2	Total	65,000,000.00	65,000,000.00	65,000,000.00	65,000,000.00
3	" " Arithmetic	65,000,000.00	65,000,000.00
4
5
6	Brackets	Rates	--- Arithmetic ---
7	cell b7 is blank		By Bracket	Cumulative
8	0	7	35,000,000.00	35,000,000.00
9	5,000,000	6	30,000,000.00	65,000,000.00
10	25,000,000	5	0.00	65,000,000.00
11	100,000,000	4	0.00	65,000,000.00
12	250,000,000	3	0.00	65,000,000.00
13	500,000,000	2	0.00	65,000,000.00
14	1,000,000,000	1	0.00	65,000,000.00
15	1E+308
16
7a

Cell Formulas
Range	Formula
B2	B2	=SUMPRODUCT(--(B1>A8:A14),B1-A8:A14,B8:B14-B7:B13)
C2	C2	=SUMPRODUCT(--(B1>aB),B1-aB,aR)
D2	D2	=SUM(IF(B1>aB,(B1-aB)*aR))
E2	E2	=SUM((B1>aB)*(B1-aB)*aR)
B3	B3	=D11
C3	C3	=SUM(C8:C11)
C8:C14	C8	=MAX(0,MIN($B$1,A9)-A8)*B8
D8	D8	=MAX(0,MIN($B$1,A9)-A8)*B8+N(D7)
D9:D14	D9	=MAX(0,MIN($B$1,A10)-A9)*B9+D8
A15	A15	=BigNum

Commission2022.xlsm
	A	B	C
17	Number	1,000,000,000.00	2,880,000,000.00
18
7a

Cell Formulas
Range	Formula
C17	C17	=SUM((B17>aB)*(B17-aB)*aR)

 

[Dave Patton]

A15 is the number as shown. The named information BigNum is =9.99999999999999E+307

The formulas is D2 and E2 work as shown if you have a current version of Excel.

Your profile does not show the version of Excel that you use!

 

[Dave Patton]

Commission2022.xlsm
	A	B	C
1
2		Total Units:	10,000,000
3		Total Costs:	65,000,000
7a

Cell Formulas
Range	Formula
C3	C3	=SUM((C2>aB)*(C2-aB)*aR)

For details and alternative structures for the formula, see the previous post.
N.B. aB and aR are named arrays of the Bracket information and Rate Differential information as mentioned in previous post.