I have a tiered fee schedule and I'm trying to calculate an IF formula to
capture the different levels. Here are my conditions for an account balance
example:

My Account balance is $1000 (C1)
A: Bill 2% (A1) for the FIRST $250 (B1)
B: Bill 1% (A2) for the NEXT $500 (B2)
C: Bill 0.5% (A3) for the NEXT $750 (B3)
D: Bill 0.25% (a4) THEREAFTER

Based off my fee schedule, my bill should be $11.25 --
((A1*B1)+(B2*A2)+((C1-(B2+B1))*A3)). But this formula wouldn't work if C1
was $200, $450, $1600, etc.... What's the correct IF formula? Any help
would be appreciated

Try this...

=IF(C1<=B1,A1*C1,IF(C1<=(B1+B2),A1*B1+A2*(C1-B1),IF(C1<=(B1+B2+B3),A1*B1+A2*B2+A3*(C1-(B1+B2)),A1*B1+A2*B2+A3*B3+A4*(C1-(B1+B2+B3)))))

Hope this helps,

Hutch

"Evan" wrote:

I have a tiered fee schedule and I'm trying to calculate an IF formula to
capture the different levels. Here are my conditions for an account balance
example:

My Account balance is $1000 (C1)
A: Bill 2% (A1) for the FIRST $250 (B1)
B: Bill 1% (A2) for the NEXT $500 (B2)
C: Bill 0.5% (A3) for the NEXT $750 (B3)
D: Bill 0.25% (a4) THEREAFTER

Based off my fee schedule, my bill should be $11.25 --
((A1*B1)+(B2*A2)+((C1-(B2+B1))*A3)). But this formula wouldn't work if C1
was $200, $450, $1600, etc.... What's the correct IF formula? Any help
would be appreciated

This formula should do what you want...

=A1*MIN(B1,C1)+A2*MIN(B2,MAX(0,C1-B1))+A3*MIN(B3,MAX(0,C1-B1-B2))+A4*MAX(0,C1-B1-B2-B3)

Rick


"Evan" wrote in message
...
I have a tiered fee schedule and I'm trying to calculate an IF formula to
capture the different levels. Here are my conditions for an account
balance
example:

My Account balance is $1000 (C1)
A: Bill 2% (A1) for the FIRST $250 (B1)
B: Bill 1% (A2) for the NEXT $500 (B2)
C: Bill 0.5% (A3) for the NEXT $750 (B3)
D: Bill 0.25% (a4) THEREAFTER

Based off my fee schedule, my bill should be $11.25 --
((A1*B1)+(B2*A2)+((C1-(B2+B1))*A3)). But this formula wouldn't work if C1
was $200, $450, $1600, etc.... What's the correct IF formula? Any help
would be appreciated

Try this:

..........A...........B............C.............D ...
1......2%.........0...........=A1..........1000
2......1%.........250......=A2-A1............
3......0.5%......750......=A3-A2............
4......0.25%....1500....=A4-A3............

=SUMPRODUCT(--(D1B1:B4),(D1-B1:B4),C1:C4)

See this:

http://mcgimpsey.com/excel/variablerate.html


--
Biff
Microsoft Excel MVP


"Evan" wrote in message
...
I have a tiered fee schedule and I'm trying to calculate an IF formula to
capture the different levels. Here are my conditions for an account
balance
example:

My Account balance is $1000 (C1)
A: Bill 2% (A1) for the FIRST $250 (B1)
B: Bill 1% (A2) for the NEXT $500 (B2)
C: Bill 0.5% (A3) for the NEXT $750 (B3)
D: Bill 0.25% (a4) THEREAFTER

Based off my fee schedule, my bill should be $11.25 --
((A1*B1)+(B2*A2)+((C1-(B2+B1))*A3)). But this formula wouldn't work if C1
was $200, $450, $1600, etc.... What's the correct IF formula? Any help
would be appreciated

On Mon, 30 Jun 2008 10:13:01 -0700, Evan
wrote:

I have a tiered fee schedule and I'm trying to calculate an IF formula to
capture the different levels. Here are my conditions for an account balance
example:

My Account balance is $1000 (C1)
A: Bill 2% (A1) for the FIRST $250 (B1)
B: Bill 1% (A2) for the NEXT $500 (B2)
C: Bill 0.5% (A3) for the NEXT $750 (B3)
D: Bill 0.25% (a4) THEREAFTER

Based off my fee schedule, my bill should be $11.25 --
((A1*B1)+(B2*A2)+((C1-(B2+B1))*A3)). But this formula wouldn't work if C1
was $200, $450, $1600, etc.... What's the correct IF formula? Any help
would be appreciated



Try this for a more generalizable solution:

Set up a table:

0 0 2%
250 5 1%
750 10 0.50%
1500 13.75 0.25%

Name it Tbl.

Column 2 represents the amount paid on the value in column 1; so for $250 the
fee would be 2%*250= 5
For 750 the fee would be 1%*(750-250) + 5
For 1500 the fee would be .5%*(1500-750) + 10


Then, use this formula:

=VLOOKUP(C1,Tbl,2)+(C1-VLOOKUP(C1,Tbl,1))*VLOOKUP(C1,Tbl,3)



--ron

Could someone help me with this please, it is driving me up the wall.

I need to charge a client a fee of 0.77% if their account balance is
$0-$50000. But for every dollar over 50000, the fee becomes 0.1% plus the 385 (50000*0.77%). Can I do this using the IF function?