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 about the sencond part?

Also, do you want it to multiply the numbers accordingly or just give you percentages?

 

[COwen]

Just as a note, .9% would be 90%% (not that you needed to know that anyway.

 

[tammyanthos]

What about the sencond part?

can it do both ? give me the total $ and what the % comes out to? for example maybe an account value of $625,000 = .725% or $4531.25

 

[COwen]

Sure? I will see what I can do.

 

[tammyanthos]

Sure? I will see what I can do.

Thank you!!!

 

[COwen]

$625,000 = .725% or $4531.25

Where are you getting this number? .725?

 

[tammyanthos]

Where are you getting this number? .725?

I made it up just as an example

 

[COwen]

Oh, I see. What percentages would be applied to 625,000? e.g.150,000*.5%, 250,000*.6%...?

 

[tammyanthos]

Oh, I see. What percentages would be applied to 625,000? e.g.150,000*.5%, 250,000*.6%...?

Like this:

 

[COwen]

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%"))