Charge Percent on Sliding Scale

[CreativeRova]

Hi Excel people,

I am in need of the formula which will allow me to calculate the amount to be charged once it falls between a range. Each separate range gets charged at a different rate.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Investment
[/TD]
[TD][/TD]
[TD]$173,543
[/TD]
[TD]$2,620.76
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Scale
[/TD]
[TD]Fee Scale
[/TD]
[TD]Investment Amount
[/TD]
[TD]Fee $
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$0 - $99,999
[/TD]
[TD]1.65%
[/TD]
[TD]$99,999
[/TD]
[TD]$1,649.98
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$100,000 - $249,999
[/TD]
[TD]1.32%
[/TD]
[TD]$73,544
[/TD]
[TD]$970.78
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$250,000 - $499,999
[/TD]
[TD]1.10%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$500,000 - $999,999
[/TD]
[TD].55%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]$1,000,000 and above
[/TD]
[TD].33%
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So above i want to change the Green section it will then auto update the red sections. The Fee Scale is only charged on the component that is between that range. i.e if it is over 1 million then all 5 scales would have numbers in there. the blue section is the total of Fee $

Thank you for your help.

 

[CreativeRova]

Hi Excel people,

Slight change to the request above. I now need all the above in 1 cell. Enter the amount in the green section a formula in the blue answer cell that takes in to account all of the percentages and ranges.

Admin people let me know if require a new thread.

 

[AhoyNC]

See if this might work for you. You need to check the math to make sure I set up the base fee for each bracket as you want (see cells J4 to J8), if wrong just change the numbers in the formula in D1 to what you need.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Investment		$173,543	$2,620.76
2
3	Scale	Fee Scale	Investment Amount	Fee $						Base for Bracket
4	$0 - $99,999	1.65%	$99,999	$1,649.98		99,999	99,999	1.65%	1649.98	1649.98
5	$100,000 - $249,999	1.32%	$73,544	$970.78		249,999	150,000	1.32%	1980.00	3629.98
6	$250,000 - $499,999	1.10%				499,999	250,000	1.10%	2750.00	6379.98
7	$500,000 - $999,999	0.55%				999,999	500,000	0.55%	2750.00	9129.98
8	$1,000,000 and above	0.33%				1,000,000	1	0.33%	0.00	9129.99
9
Sheet

 

[CreativeRova]

AhoyNC,

You my friend are an absolute genius.

Thank you!!!

 

[AhoyNC]

Your welcome. Glad that worked for you. I know you said you wanted it all in one cell, but usually with something like this it's better to have the rates in a separate table so you can make changes easily if the rates change.
Thanks for the feed back.