Complex IF formula

[Evan]

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

 

[Tom Hutchins]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[T. Valko]

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

 

[Ron Rosenfeld]

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