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]

Hmmm...this is what it gave me for a $225,000 account --- I added the % below which doesn't seem to be right for $225,000 --- wouldn't it be something more like .57% since it's .5% for the first $150,000 and .6% for the other $75,000?

View attachment 10671

mind you, I'm not very good at math so I could be way off ... lol

 

[COwen]

You're right it should be .533% I am looking into that right now.

 

[COwen]

Perhaps this...

=IF(D1>150000,IF(D1<=250000,150000*0.5%+(((D1-150000))*0.6%),IF(D1<=500000,(((150000*0.5%)+((100000)*0.6%)+((D1-(250000))*0.7%))&"; Average Charge of: "&TEXT(AVERAGE(0.5%,0.6%,0.7%),"0.00%")),IF(D1<1000000,(150000*0.5%)+100000*0.6%+250000*0.7%+((D1-500000)*0.8%)&"; Average Charge of: "&TEXT(AVERAGE(0.5%,0.6%,0.7%,0.8%),"0.00%"),D1*0.9%&"; Average Charge of: 0.90%"))),D1*0.5%)

 

[tammyanthos]

Perhaps this...

=IF(D1>150000,IF(D1<=250000,150000*0.5%+(((D1-150000))*0.6%),IF(D1<=500000,(((150000*0.5%)+((100000)*0.6%)+((D1-(250000))*0.7%))&"; Average Charge of: "&TEXT(AVERAGE(0.5%,0.6%,0.7%),"0.00%")),IF(D1<1000000,(150000*0.5%)+100000*0.6%+250000*0.7%+((D1-500000)*0.8%)&"; Average Charge of: "&TEXT(AVERAGE(0.5%,0.6%,0.7%,0.8%),"0.00%"),D1*0.9%&"; Average Charge of: 0.90%"))),D1*0.5%)

that worked! at least, I think it did! and of course, my boss just changed the tier structure on me so it's now going to be this:

Tiers	%
$0-$100,000	0.40%
$100,000-$250,000	0.35%
$250,000-$500,000	0.30%
$500,000-$1,000,000	0.20%
$1,000,000-$1,500,000	0.175%

 

[tammyanthos]

that worked! at least, I think it did! and of course, my boss just changed the tier structure on me so it's now going to be this:

Tiers	%
$0-$100,000	0.40%
$100,000-$250,000	0.35%
$250,000-$500,000	0.30%
$500,000-$1,000,000	0.20%
$1,000,000-$1,500,000	0.175%

So now do I have to go in and change all the values? and since it's now a decreasing scale, does that make a difference in the formula itself? Thanks for all your help!!!

 

[COwen]

No Problem. Please wait...

 

[COwen]

No, I'll do it---it is only a couple since only the rates changed. It does not effect the formula because it will still be multiplying the account$ by the rate.

Happy I could help.

I'll get this to you momentarily

 

[COwen]

This should do it.

=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%))&"; Average Charge of: "&TEXT(AVERAGE(0.4%,0.35%,0.3%),"0.00%")),IF(D1<1000000,(150000*0.4%)+100000*0.35%+250000*0.3%+((D1-500000)*0.2%)&"; Average Charge of: "&TEXT(AVERAGE(0.4%,0.35%,0.3%,0.2%),"0.00%"),D1*0.18%&"; Average Charge of: 0.180%"))),D1*0.4%)

 

[tammyanthos]

This should do it.

=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%))&"; Average Charge of: "&TEXT(AVERAGE(0.4%,0.35%,0.3%),"0.00%")),IF(D1<1000000,(150000*0.4%)+100000*0.35%+250000*0.3%+((D1-500000)*0.2%)&"; Average Charge of: "&TEXT(AVERAGE(0.4%,0.35%,0.3%,0.2%),"0.00%"),D1*0.18%&"; Average Charge of: 0.180%"))),D1*0.4%)

 

[tammyanthos]

THANK YOU! It appears to work up to $250,000, but after that it does not return a value