SUMPRODUCT formula & chart for blended tier pricing

[tammyanthos]

Blended tier pricing

I need to create a chart and formula that calculates what the price = based on the $ amount of the account

e.g. accounts up to $100k = .50, $100k-$250k = .60, etc. so an account of $175k would be.50 for the first $100k and .60 for the other $75k ?

 

[tammyanthos]

T202004a.xlsm
	A	B	C	D	E	F	G
1	Tiers	Bps	%	260,000.00	955.00
2
3	0	40	0.4000%
4	100,000	35	0.3500%			400.00	400.00
5	250,000	30	0.3000%			525.00	525.00
6	500,000	20	0.2000%			30.00	750.00
7	1,000,000	17.5	0.1750%				1,000.00
8							218.58
9				1,124,900.00	2,893.58	955.00	2,893.58
10					2,893.58
11
2b

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(--(D1>A3:A7),D1-A3:A7,C3:C7-C2:C6)
F4:F5	F4	=(A4-A3)*C3
F6	F6	=(D1-A5)*C5
G4:G7	G4	=(A4-A3)*C3
G8	G8	=(D9-A7)*C7
F9:G9	F9	=SUM(F4:F8)
E9	E9	=SUMPRODUCT(--(D9>{0;100000;250000;500000;1000000}),D9-{0;100000;250000;500000;1000000},{0.004;-0.0005;-0.0005;-0.001;-0.00025})
E10	E10	=SUMPRODUCT(--(D9>A3:A7),D9-A3:A7,C3:C7-C2:C6)

THAT'S AMAZING! CAN YOU EMAIL ME THE ACTUAL SPREADSHEET?

You are very welcome!

This is my first time posting here, can I provide feedback or how does that work?

 

[tammyanthos]

can you email me the spreadsheet with the SUMPRODUCT table & formula?

 

[tammyanthos]

You are very welcome!

I'm so sorry, I just realized I had a typo on the last tier - how do I fix the formula if the last tier is supposed to be $1,000,000 - $5,000,000 ?

 

[Dave Patton]

"can you email me the spreadsheet with the SUMPRODUCT table & formula? "

You can copy the information from the post
You may have to add the formulas that are shown.

 

[COwen]

I'm so sorry, I just realized I had a typo on the last tier - how do I fix the formula if the last tier is supposed to be $1,000,000 - $5,000,000 ?

You shouldn't need to fix it. It will calculate either way. If you do not want it to calculate above $5,000,000, then I can edit it to say something like "Oops, that is to big to chew" or something similar.

 

[tammyanthos]

You shouldn't need to fix it. It will calculate either way. If you do not want it to calculate above $5,000,000, then I can edit it to say something like "Oops, that is to big to chew" or something similar.

That would be funny! But not necessary, it would be great to get an account greater than $5M!!! Thanks again for everything, I really appreciate all your hard work on this In this forum, do you rate people's answers or anything?

 

[tammyanthos]

I show the Sumproduct formula; one uses the Rate table and the other includes the table information within the formula.

T202004a.xlsm
	A	B	C	D	E
1	Tiers	Bps	%	260,000.00	955.00
2
3	0	40	0.4000%
4	100,000	35	0.3500%
5	250,000	30	0.3000%
6	500,000	20	0.2000%
7	1,000,000	17.5	0.1750%
8
9				1,124,900.00	2,893.58
10
2b

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(--(D1>A3:A7),D1-A3:A7,C3:C7-C2:C6)
E9	E9	=SUMPRODUCT(--(D9>{0;100000;250000;500000;1000000}),D9-{0;100000;250000;500000;1000000},{0.004;-0.0005;-0.0005;-0.001;-0.00025})

Dave - should the first SUMPRODUCT formula for the table have C2 in it which is blank?

 

[Dave Patton]

What is your concern about a cell such as C2 being blank?

You can copy the posted information to a blank sheet.
I added an arithmetic version.

T202004a.xlsm
	A	B	C	D	E	F	G
1	Tiers	Bps	%	260,000.00	955.00
2
3	0	40	0.4000%
4	100,000	35	0.3500%			400.00	400.00
5	250,000	30	0.3000%			525.00	525.00
6	500,000	20	0.2000%			30.00	750.00
7	1,000,000	17.5	0.1750%				1,000.00
8							218.58
9				1,124,900.00	2,893.58	955.00	2,893.58
10				or	2,893.58
11				or	2,893.58
2b

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(--(D1>A3:A7),D1-A3:A7,C3:C7-C2:C6)
F4:F5	F4	=(A4-A3)*C3
F6	F6	=(D1-A5)*C5
G4:G7	G4	=(A4-A3)*C3
G8	G8	=(D9-A7)*C7
F9:G9	F9	=SUM(F4:F8)
E9	E9	=SUMPRODUCT(--(D9>{0;100000;250000;500000;1000000}),D9-{0;100000;250000;500000;1000000},{0.004;-0.0005;-0.0005;-0.001;-0.00025})
E10	E10	=SUMPRODUCT(--(D9>A3:A7),D9-A3:A7,C3:C7-C2:C6)
E11	E11	=(D9>1000000)*(D9-1000000)*0.00175+(D9>500000)*MIN(500000,D9-500000)*0.002+(D9>250000)*MIN(250000,D9-250000)*0.003+(D9>100000)*MIN(150000,D9-150000)*0.0035+(D9>0)*MIN(100000,D9-0)*0.004

 

[tammyanthos]

What is your concern about a cell such as C2 being blank?

You can copy the posted information to a blank sheet.
I added an arithmetic version.

T202004a.xlsm
	A	B	C	D	E	F	G
1	Tiers	Bps	%	260,000.00	955.00
2
3	0	40	0.4000%
4	100,000	35	0.3500%			400.00	400.00
5	250,000	30	0.3000%			525.00	525.00
6	500,000	20	0.2000%			30.00	750.00
7	1,000,000	17.5	0.1750%				1,000.00
8							218.58
9				1,124,900.00	2,893.58	955.00	2,893.58
10				or	2,893.58
11				or	2,893.58
2b

Cell Formulas
Range	Formula
E1	E1	=SUMPRODUCT(--(D1>A3:A7),D1-A3:A7,C3:C7-C2:C6)
F4:F5	F4	=(A4-A3)*C3
F6	F6	=(D1-A5)*C5
G4:G7	G4	=(A4-A3)*C3
G8	G8	=(D9-A7)*C7
F9:G9	F9	=SUM(F4:F8)
E9	E9	=SUMPRODUCT(--(D9>{0;100000;250000;500000;1000000}),D9-{0;100000;250000;500000;1000000},{0.004;-0.0005;-0.0005;-0.001;-0.00025})
E10	E10	=SUMPRODUCT(--(D9>A3:A7),D9-A3:A7,C3:C7-C2:C6)
E11	E11	=(D9>1000000)*(D9-1000000)*0.00175+(D9>500000)*MIN(500000,D9-500000)*0.002+(D9>250000)*MIN(250000,D9-250000)*0.003+(D9>100000)*MIN(150000,D9-150000)*0.0035+(D9>0)*MIN(100000,D9-0)*0.004

Thank you! I copied it over & it works perfectly, thanks again, much appreciated!

 

[Dave Patton]

Thanks for the feedback. Dave