SUMPRODUCT formula & chart for blended tier pricing

tammyanthos

New Member
Joined
Apr 6, 2020
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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 ?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
I do not fully understand your requirements. Here is a formula that will find the price up to $250k...you did not provide rates after that.
Code:
=TEXT(IF(J19>100000,IF(J19<=250000,100000*0.5+((J19-100000)*0.6),TEXT(J19,"$#,000.00")&" Is larger than $250k"),J19*0.5),"$#,000.00")
I do not know what you need. please clarify your requirements for better help.
 
Upvote 0
I do not fully understand your requirements. Here is a formula that will find the price up to $250k...you did not provide rates after that.
Code:
=TEXT(IF(J19>100000,IF(J19<=250000,100000*0.5+((J19-100000)*0.6),TEXT(J19,"$#,000.00")&" Is larger than $250k"),J19*0.5),"$#,000.00")
I do not know what you need. please clarify your requirements for better help.
https://www.schwabpt.com/public/file/P-9043995/spt010771.pdf

this is an example of what I need to build and wanted to use SUMPRODUCT formula to do so
 
Upvote 0
I am not sure how to get a SUMPRODUCT to break apart the values if they are above and below the criteria. My formula does what you want it to do. If you give me more rates, (e.g. >$250k) then I will be happy to build a full formula.
 
Upvote 0
I am not sure how to get a SUMPRODUCT to break apart the values if they are above and below the criteria. My formula does what you want it to do. If you give me more rates, (e.g. >$250k) then I will be happy to build a full formula.

Thank you!

I would want to be able to plug in a value of X (e.g. $175,000) in somewhere on the excel sheet and have it determine the total % we should charge

$0-$150,000 = .5%
$150,000-$250,000 = .6%
$250,000-$500,000 = .7%
$500,000-$1,000,000 = .8%
Greater than $1,000,000 = 9%%
 
Upvote 0
Do you mean 9% for >1,000,000? 9%% is 0.0009...

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

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top