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

#### Sorbital

##### New Member
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"

-Sorbi

### Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

#### SteveO59L

##### Well-known Member
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

##### MrExcel MVP
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
AB
1AmountFee
212500118125.15
Sheet
Excel Workbook
FG
100.175
21250010.145
32500010.135
45000010.115
510000010.1
FEE TABLE

#### barry houdini

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
Here's how it might work.....
Book2
ABCD
1Account Value\$395,725.97
2
3lower boundratetotal
4
501.75%\$2,187.50
61250001.45%\$1,812.50
72500001.35%\$1,967.30
85000001.15%\$0.00
910000001%\$0.00
10
11Total fee\$5,967.30
12
13option 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

Replies
12
Views
1K

1,191,501
Messages
5,986,926
Members
440,067
Latest member
Swatts1

### 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.

### Which adblocker are you using?

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

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