Complex IF

[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

 

[Pete_UK]

Fortunately, I've seen this in another group and also seen that you
had a response there, so I won't waste time tackling it - please do
NOT multi-post.

Pete

 

[Gary Brown]

If Cell B4 = a very large # such as $1,000,000, the following formula will
work.

=($A1*IF($C$1-$B1>0,$B1,$C$1))+(IF($C$1-SUM($B$1:$B2)>0,$A2*$B2,IF($C$1-SUM($B$1:$B1)<=0,0,($C$1-SUM($B$1:$B1))*$A2)))+(IF($C$1-SUM($B$1:$B3)>0,$A3*$B3,IF($C$1-SUM($B$1:$B2)<=0,0,($C$1-SUM($B$1:$B2))*$A3)))+(IF($C$1-SUM($B$1:$B4)>0,$A4*$B4,IF($C$1-SUM($B$1:$B3)<=0,0,($C$1-SUM($B$1:$B3))*$A4)))

This formula is just the addition of 4 individual parts, one for each of
your conditions...

A: Bill 2% (A1) for the FIRST $250 (B1) =IF($C$1-SUM($B$1:$B1)>0,$A1*$B1,IF($C$1-SUM($B$1:$B1)<=0,0,($C$1-SUM($B$1:$B1))*$A1))
B: Bill 1% (A2) for the NEXT $500 (B2) =IF($C$1-SUM($B$1:$B2)>0,$A2*$B2,IF($C$1-SUM($B$1:$B1)<=0,0,($C$1-SUM($B$1:$B1))*$A2))
C: Bill 0.5% (A3) for the NEXT $750 (B3) =IF($C$1-SUM($B$1:$B3)>0,$A3*$B3,IF($C$1-SUM($B$1:$B2)<=0,0,($C$1-SUM($B$1:$B2))*$A3))
D: Bill 0.25% (a4) THEREAFTER

=IF($C$1-SUM($B$1:$B4)>0,$A4*$B4,IF($C$1-SUM($B$1:$B3)<=0,0,($C$1-SUM($B$1:$B3))*$A4))

--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown

 

[Gary Brown]

Sorry,
the first part of the formula should be...
=IF($C$1-SUM($B$1:$B1)>0,$A1*$B1,$A1*$C$1)
not
=IF($C$1-SUM($B$1:$B1)>0,$A1*$B1,IF($C$1-SUM($B$1:$B1)<=0,0,($C$1-SUM($B$1:$B1))*$A1))
--
Hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown

 

[Brad]

Just to reiterate

A B C
2.00% 250 1000
1.00% 750
0.50% 1500
0.25%

In d1 enter

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

and you will get 11.25.

 

[Brad]

What number did this not work for??