Complex IF

  • Thread starter Thread starter Evan
  • Start date Start date
E

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
 
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
 
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
Click to expand...
=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
 
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
 
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.
 
Back
Top