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" <(E-Mail Removed)> wrote in message
newsE43B1FB-6139-43D4-9DB7-(E-Mail Removed)...
>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(--(D1>B1:B4),(D1-B1:B4),C1:C4)

See this:

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


--
Biff
Microsoft Excel MVP


"Evan" <(E-Mail Removed)> wrote in message
newsE43B1FB-6139-43D4-9DB7-(E-Mail Removed)...
>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 <(E-Mail Removed)>
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