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 ?

 

[COwen]

What is the rest of the result in D8?

 

[tammyanthos]

 

[tammyanthos]

anything above $250,000 doesn't return a value ???

 

[COwen]

Part of the result is hidden. What is the rest?

 

[tammyanthos]

View attachment 10675

Part of the result is hidden. What is the rest?

 

[COwen]

Not the formula. The result is partially hidden. Expand the D column to 15.

 

[tammyanthos]

View attachment 10676

Tiers	Bps	%	$260,000.00​
$0-$100,000	40	0.40%
$100,000-$250,000	35	0.35%
$250,000-$500,000	30	0.30%
$500,000-$1,000,000	20	0.20%
$1,000,000-$1,500,000	17.5	0.175%
		Total:	980; Average Charge of: 0.35%
		%	#VALUE!

 

[COwen]

Okay. I see. Here try this formula in D8:

=IF(D1>150000,IF(D1<=250000,150000*0.4%+(((D1-150000))*0.35%),IF(D1<=500000,(((150000*0.4%)+((100000)*0.35%)+((D1-(250000))*0.3%))),IF(D1<1000000,(150000*0.4%)+100000*0.35%+250000*0.3%+((D1-500000)*0.2%),D1*0.18%))),D1*0.4%)

and this formula in D9:

=D8/D1

 

[COwen]

Format D8 to Currency format; and D9 to Percentage format.

 

[tammyanthos]

Okay. I see. Here try this formula in D8:

=IF(D1>150000,IF(D1<=250000,150000*0.4%+(((D1-150000))*0.35%),IF(D1<=500000,(((150000*0.4%)+((100000)*0.35%)+((D1-(250000))*0.3%))),IF(D1<1000000,(150000*0.4%)+100000*0.35%+250000*0.3%+((D1-500000)*0.2%),D1*0.18%))),D1*0.4%)

and this formula in D9:

=D8/D1