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]

This should do the trick. Assuming your value to test is in D1...

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

wow, that's quite a formula! I will test & let you know, thank you SO much!

 

[tammyanthos]

This should do the trick. Assuming your value to test is in D1...

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

 

[tammyanthos]

This should do the trick. Assuming your value to test is in D1...

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

this is probably a dumb question but where do I add your formula?

Tiers	Pricing	Amount$	$ 100,000.00
$0-$150,000	0.50%​
$150,000-$250,000	0.60%​
$250,000-$500,000	0.70%​
$500,000-$1,000,000	0.80%​
Greater than $1,000,000	0.90%​
#VALUE!​

 

[COwen]

No problem---Hope it works...

 

[COwen]

Uhh...Hold on I'll tell you soon.

 

[COwen]

Add it in the bottom right row. Then change all of the references (I know, it's a lot!) to the cell which contains the "$100,000" next to "Amount$"

 

[tammyanthos]

Add it in the bottom right row. Then change all of the references (I know, it's a lot!) to the cell which contains the "$100,000" next to "Amount$"

bottom right row

Add it in the bottom right row. Then change all of the references (I know, it's a lot!) to the cell which contains the "$100,000" next to "Amount$"

like this?

 

[COwen]

My bad...
Try this:

=IF(D1>150000,IF(D1<=250000,150000*0.5%+((D1-(D1-150000))*0.6%),IF(D1<=500000,(((150000*0.5%)+((((D1-150000-(D1-(250000+150000)))))*0.6%)+((D1-(250000+150000))*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%)

 

[COwen]

Yes, like that!

 

[tammyanthos]

My bad...
Try this:

=IF(D1>150000,IF(D1<=250000,150000*0.5%+((D1-(D1-150000))*0.6%),IF(D1<=500000,(((150000*0.5%)+((((D1-150000-(D1-(250000+150000)))))*0.6%)+((D1-(250000+150000))*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%)

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?