Tiered Pricing based on usage

[Welsh Mark3]

I'm looking to build a formula that calculates pricing based on tiered usage.
Cell F7 will contain usage (a number between 1 and 250,000) Cell f9 is where I would like to insert the formula. The pricing structure I would like to use is laid out below.

Example 1 if usage is 30,000 the output would be 30,000 * 1
Example 2 If usage is 175,000 the output would be (50,000 * 1) + (50,000 * 0.75) + (75,000 * 0.5) = 125,000

Column G Column H Column I Column J

Cost Per API Call < 50,000	Cost Per API Call > 50,000 <100,000	Cost Per API Call > 100,000 < 200,00	Cost Per API Call > 200,000
$1.00	$0.75	$0.50	$0.25

I have tried to read similar posts but they seem slightly different from this request. Your help is appreciated.

 

[Eric W]

The recommended method is to build a table like this:

Book1
	A	B	C	D	E	F	G
1	Calls	Cost		Low	High	Cost	Delta
2	30000	$ 30,000.00		0	50000	$ 1.00	$ 1.00
3	175000	$ 125,000.00		50000	100000	$ 0.75	$ (0.25)
4				100000	200000	$ 0.50	$ (0.25)
5				200000	999999999	$ 0.25	$ (0.25)
Sheet3

Cell Formulas
Range	Formula
B2:B3	B2	=SUMPRODUCT((A2-$D$2:$D$5),--(A2>$D$2:$D$5),$G$2:$G$5)
G2:G5	G2	=F2-N(F1)

There are ways to do it without a table, but it makes the formula more complicated. Let me know if that's of interest.

 

[Dave Patton]

T202010b.xlsm
	C	D	E	F	G
1		Brackets	Rate
2	Blank -->
3		0	1.00
4		50,000	0.75
5		100,000	0.50
6		200,000	0.25
7				175,000.00	125,000.00
8
7a

Cell Formulas
Range	Formula
G7	G7	=SUMPRODUCT(--(F7>D3:D6),F7-D3:D6,E3:E6-E2:E5)

 

[Dave Patton]

T202010b.xlsm
	C	D	E	F	G
1		Brackets	Rate
2	Blank -->
3		0	1.00
4		50,000	0.75
5		100,000	0.50
6		200,000	0.25
7				175,000.00	125,000.00
8					125,000.00
9
7a

Cell Formulas
Range	Formula
G7	G7	=SUMPRODUCT(--(F7>D3:D6),F7-D3:D6,{1;-0.25;-0.25;-0.25})
G8	G8	=MIN(F7,50000)*1+MAX(0,MIN(50000,F7-50000))*0.75+MAX(0,MIN(100000,F7-100000))*0.5+MAX(0,F7-200000)*0.25

 

[Welsh Mark3]

I may have phrased my requirement wrong - All the formulas work but not in the way I'm looking to implement.
They all seem to work by multiplying the number of calls by the price as per the bracket.

What I'm trying to do is if someone make 75000 calls, the first 50,000 charged at X and the remaining 25000 charged at Y

 

[Eric W]

All the formulas posted do exactly that. For example, your formula of 175,000 reduces to 50,000 * $1.00 + 50,000 * $.75 + 75,000 * $.50 = $125,000, just like you said. If it only looked at the bottom tier it would be 175,000 * $.50 = $87,500. None of the formulas give you that.

 

[pgc01]

I may have phrased my requirement wrong - All the formulas work but not in the way I'm looking to implement.
They all seem to work by multiplying the number of calls by the price as per the bracket.

What I'm trying to do is if someone make 75000 calls, the first 50,000 charged at X and the remaining 25000 charged at Y

Hi Welsh Mark3

Please post an example that you have tested and that did not work with the solutions posted here.

 

[Welsh Mark3]

Ok after some testing the formula is working. MIN(F7,50000)*1+MAX(0,MIN(50000,F7-50000))*0.75+MAX(0,MIN(100000,F7-100000))*0.5+MAX(0,F7-200000)*0.25

Is there a way to change the output so the minimum value is $100,000 or greater

 

[Dave Patton]

Click on the icon below the F(x) below and pasted to cell A1 on a clean sheet.
Review the formulas and ensure that they do what you require.
Use one of these formulas or edit and use one of the prior suggestions.

T202010b.xlsm
	A	B	C	D	E	F	G
1				Brackets	Rate
2			Blank -->
3				0	1.00
4				50,000	0.75
5				100,000	0.50
6				200,000	0.25
7						100,000.00	87,500.00
8							87,500.00
9
10					Minimum	100,000	100,000.00
11							100,000.00
12
7a

Cell Formulas
Range	Formula
G7	G7	=SUMPRODUCT(--(F7>D3:D6),F7-D3:D6,{1;-0.25;-0.25;-0.25})
G8	G8	=MIN(F7,50000)*1+MAX(0,MIN(50000,F7-50000))*0.75+MAX(0,MIN(100000,F7-100000))*0.5+MAX(0,F7-200000)*0.25
G10	G10	=MAX(F10,SUMPRODUCT(--(F7>D3:D6),F7-D3:D6,{1;-0.25;-0.25;-0.25}))
G11	G11	=MAX(100000,MIN(F7,50000)*1+MAX(0,MIN(50000,F7-50000))*0.75+MAX(0,MIN(100000,F7-100000))*0.5+MAX(0,F7-200000)*0.25)

 

[Welsh Mark3]

This is great - Thank you all for your input