Average cost on a tiered pricing table

[littleowl]

Hi All, hope you can help me

Can you please help with the formulas to find the average cost a client will pay if they purchase initially let's say 13,000 and then also if this quantity increases/decreases along the way?

Tier 1: from 1 to 5,999 at $10.50
Tier 2: from 6,000 to 7,999 at $10.00
Tier 3: from 8,000 to 9,999 at $9.50
Tier 4: from 10,000 to 12,499 at $9.00
Tier 5: from 12,500 to 14,999 at $8.50
Tier 6: from 15,000 to 19,999 at $8.00
Tier 7: from 20,000 or above at $7.50


Thanks.

 

[Dave Patton]

You can try the following. I have not used a MacOS.
The following can be written with SumProduct.
The Lambda may useful if you have a number of rate structures.

N.B. You can copy the post example to a clean sheet. Click below the f(x) in the heading and move to your sheet's cell A1, and paste.

Use Name Manager to Name the Lambda function and put the Lambda information in the Value = area.

Commissions 2024.xlsm
	A	B	C	D	E	F	G
1
2
3		Quantity	Amount	Average		0	10.50
4		13,000	$128,750.00	9.9038		6,000	10.00
5		20,000	$185,750.00	9.2875		8,000	9.50
6						10,000	9.00
7		13,000	$128,750.00			12,500	8.50
8		20,000	$185,750.00	9.2875		15,000	8.00
9						20,000	7.50
10
2a

Cell Formulas
Range	Formula
C4:C5	C4	=SUM((B4>$F$3:$F$9)*(B4-$F$3:$F$9)*($G$3:$G$9-$G$2:$G$8))
D4:D5	D4	=C4/B4
C7:C8	C7	=GrossP($F$3:$G$9,B7)
D8	D8	=GrossP_Avg(F3:G9,B8)

Lambda Functions
Name	Formula
GrossP	=LAMBDA(rngRateData,Quantity,LET(rng, rngRateData, q, Quantity, b, CHOOSECOLS(rng, 1), r, CHOOSECOLS(rng, 2), ro, VSTACK(0, DROP(r, -1)), SUM((q > b) * (q - b) * (r - ro)) ))
GrossP_Avg	=LAMBDA(rngRateData,Quantity,LET(rng, rngRateData, q, Quantity, b, CHOOSECOLS(rng, 1), r, CHOOSECOLS(rng, 2), ro, VSTACK(0, DROP(r, -1)), SUM((q > b) * (q - b) * (r - ro))/q ))

 

[Dave Patton]

The same formula built differently; you can try the version(s) that you prefer.

Commissions 2024.xlsm
	L	M	N	O
1
2	1000	10.50	10.50	10.50
3	2000	10.50	10.50	10.50
4	3000	10.50	10.50	10.50
5	4000	10.50	10.50	10.50
6	5000	10.50	10.50	10.50
7	6000	10.50	10.50	10.50
8	7000	10.43	10.43	10.43
9	8000	10.38	10.38	10.38
10	9000	10.28	10.28	10.28
11	10000	10.20	10.20	10.20
12	11000	10.09	10.09	10.09
13	12000	10.00	10.00	10.00
14	13000	9.90	9.90	9.90
15	14000	9.80	9.80	9.80
16	15000	9.72	9.72	9.72
17	16000	9.61	9.61	9.61
18	17000	9.51	9.51	9.51
19	18000	9.43	9.43	9.43
20	19000	9.36	9.36	9.36
21	20000	9.29	9.29	9.29
22	21000	9.20	9.20	9.20
23	22000	9.13	9.13	9.13
24	23000	9.05	9.05	9.05
25	24000	8.99	8.99	8.99
26	25000	8.93	8.93	8.93
27	26000	8.88	8.88	8.88
28	27000	8.82	8.82	8.82
29	28000	8.78	8.78	8.78
30	29000	8.73	8.73	8.73
31	30000	8.69	8.69	8.69
32	40000	8.39	8.39	8.39
33	50000	8.22	8.22	8.22
34	60000	8.10	8.10	8.10
35	70000	8.01	8.01	8.01
36	80000	7.95	7.95	7.95
37	90000	7.90	7.90	7.90
38	100000	7.86	7.86	7.86
39	1000000	7.54	7.54	7.54
2a

Cell Formulas
Range	Formula
M2:M39	M2	=GrossP_Avg($F$3:$G$9,L2)
N2:N39	N2	=LET(q,L2,b,$F$3:$F$9,r,$G$3:$G$9,ro,VSTACK(0,DROP(r,-1)),SUM((q>b)*(q-b)*(r-ro))/q)
O2:O39	O2	=LET(q,L2,b,{0;6000;8000;10000;12500;15000;20000},r,{10.5;-0.5;-0.5;-0.5;-0.5;-0.5;-0.5},SUM((q>b)*(q-b)*r)/q)

Lambda Functions
Name	Formula
GrossP_Avg	=LAMBDA(rngRateData,Quantity,LET(rng, rngRateData, q, Quantity, b, CHOOSECOLS(rng, 1), r, CHOOSECOLS(rng, 2), ro, VSTACK(0, DROP(r, -1)), SUM((q > b) * (q - b) * (r - ro))/q ))