This has to be so simple and I'm too stupid to figure it out

[Sorbital]

I need to charge a customer a fee based on a fee schedule:

$0 to $125,000 @ 1.75% =
$125,001 to $250,000 @ 1.45% =
$250,001 to $500,000 @ 1.35% =
$500,001 to $1,000,000 @ 1.15% =
$1,000,001 to $3,000,000 @ 1% =
Total Fee Charged =

Each line above would be a row, so that the cell would show how much was charged at each breakpoint.

I know that there is a formula to figure this out, but I'm apparently not of the mental capability to do it!!

As Clint Eastwood said, "A man's gotta know his limitations"

Thanking you in advance,

-Sorbi

 

[SteveO59L]

Are you looking for 1 formula per row, or a composite formula to take in all conditions ?


Something like

=IF(B3<=125000,B3*0.0175,IF(B3<=250000,B3*0.0145,IF(B3<=500000,B3*0.0135,IF(B3<=1000000,B3*0.015,IF(B3<=3000000,B3*0.01,"Something Else")))))

 

[Scott Huish]

Make a table like I did in F1:G5 with just the low end of each range in the first column.

With the amount in A1.
Formula in B1:
=A2*LOOKUP(A2,$F$1:$G$5)

Excel Workbook
	A	B
1	Amount	Fee
2	125001	18125.15
Sheet

Excel Workbook
	F	G
1	0	0.175
2	125001	0.145
3	250001	0.135
4	500001	0.115
5	1000001	0.1
FEE TABLE

 

[barry houdini]

So, if the amount was 700,000 would you want to calculate 1.15% of the whole amount or would you charge the first 125,000 at 1.75% the next 125,000 at 1.45% etc.?

If it's the latter look here

 

[Sorbital]

Yeah, Barry...That's what I'm looking for...

Account Value @ 6/30/2007 $395,725.97
$0 to $125,000 @ 1.75% = ??
$125,001 to $250,000 @ 1.45% = ??
$250,001 to $500,000 @ 1.35% = ??
$500,001 to $1,000,000 @ 1.15%
$1,000,001 to $3,000,000 @ 1%

That's a copy of the table I've set up, I'm not sure how the formulae would translate. Assume that I'm the LCD (lowest common denominator) of the Excel world.

 

[barry houdini]

Here's how it might work.....

Book2
	A	B	C	D
1	Account Value	$395,725.97
2
3	lower bound	rate	total
4
5	0	1.75%	$2,187.50
6	125000	1.45%	$1,812.50
7	250000	1.35%	$1,967.30
8	500000	1.15%	$0.00
9	1000000	1%	$0.00
10
11		Total fee	$5,967.30
12
13		option 2	$5,967.30
14
Sheet1

Option 2 uses the method described in the link posted above. You can get the total fee with a single formula based on the rate table, i.e.

=SUMPRODUCT(--(B1>A5:A9),B1-A5:A9,B5:B9-B4:B8)

Note: B4 should be blank or zero

...or if you want to know how much is in each band you can use the formulas in C5:C9 and then sum these values below. The result should be the same either way.

Formula in C5 copied down to C8

=MEDIAN(0,A6-A5,B$1-A5)*B5

formula in C9

=MAX(0,B$1-A9)*B5