if statement

[mdwheels]

I need to calculate based on the below chart but coming up with a negative # (I understand why) but can't figure out how to get an additional if statement to resolve or is there a better way to calculate

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Fund Balance[/TD]
[TD][/TD]
[TD]$750,000[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Up to [/TD]
[TD]$499,999[/TD]
[TD][/TD]
[TD]2.25%[/TD]
[TD]=IF(J27>500000,((500000*K29)/12),(J27*K29/12))[/TD]
[TD]937.50[/TD]
[/TR]
[TR]
[TD]From[/TD]
[TD]$500,000[/TD]
[TD]$999,999[/TD]
[TD]1.97%[/TD]
[TD]=IF(J27>999999,((500000*K30)/12),((J27-500000)*K30/12))[/TD]
[TD]410.42[/TD]
[/TR]
[TR]
[TD]From[/TD]
[TD]$1,000,000[/TD]
[TD]$2,499,999[/TD]
[TD]1.69%[/TD]
[TD]=IF(J27>2499999,((1499999*K31)/12),((J27-999999)*K31/12))[/TD]
[TD]-352.08[/TD]
[/TR]
[TR]
[TD]Over[/TD]
[TD]$2,500,000[/TD]
[TD][/TD]
[TD]1.41%[/TD]
[TD]=IF(J27<2500000,0,(J27-2500000)*K32)[/TD]
[TD]0

[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 397"]
<colgroup><col><col span="2"><col><col></colgroup><tbody></tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

How exactly is it supposed to work?
Is it that 2.25% is applied to the amounts under 500,000, then 1.97% is applied to the amounts between 500,000 and 999,999, then 1.69% is applied to the amounts between 1,000,000 and 2,499,999, and then 1.41% should be applied on any amounts over 250,000, and then you want to sum all those amounts together?

If that is the case, then I think you should have 0 in either the TRUE or FALSE of every IF statement (because if it doesn't meet that particular criteria, I don't think you want to apply any amount to it).

 

[mdwheels]

so it is a tiered fee schedule - the first $500K at highest rate and the second $500K at the next rate etc. If the amount is greater in any of the ranges - it would have the max fee for that amount

 

[Joe4]

If my prior assumptions are correct, then putting the high/low values in columns I and J, here are the four formulas I come up with (in order):

=MIN(I29,$J$27)*K29/12
=MAX(MIN(J30,$J$27)-I30,0)*K30/12
=MAX(MIN(J31,$J$27)-I31,0)*K31/12
=MAX($J$27-I32,0)*K32/12

 

[Joe4]

so

it is a tiered fee schedule - the first $500K at highest rate and the second $500K at the next rate etc. If the amount is greater in any of the ranges - it would have the max fee for that amount

So, walk me through the example you posted initially.
What total amount do you expect/want?

The formulas I provided above would return 1347.92 (from 937.50 + 410.42 + 0 + 0).

Note that you may want to round each formula to two decimal places, i.e.
=ROUND(MIN(I29,$J$27)*K29/12,2)
etc.

 

[smozgur]

If you could write a max amount (like 999.999.999) in J31, and make I28 to be 0 and J28 to be 499999 (simply using lower and upper limits) then I think following formula could work:

Cell N28:

<i28,0,1)*if(j$27<j28,(j$27-i28)*k28,(j28-i28)*k28) 12

=IF(J$27<I28,0,1)*IF(J$27<J28,(J$27-I28)*K28,(J28-I28)*K28)/12
<i28,0,1)*if(j$27<j28,(j$27-i28)*k28,(j28-i28)*k28) 12

Copy and paste into N28:N31.

Edit: LOL! It has been almost solved while I was trying to figure it out I am really not a formula pro!</i28,0,1)*if(j$27<j28,(j$27-i28)*k28,(j28-i28)*k28)></i28,0,1)*if(j$27<j28,(j$27-i28)*k28,(j28-i28)*k28)>