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

On Jun 30, 6:12*pm, 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

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

=($A1*IF($C$1-$B10,$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


"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

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


"Gary Brown" wrote:

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

=($A1*IF($C$1-$B10,$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


"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

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.
--
Wag more, bark less


"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

Gary -- Thanks. It worked.

Brad, I changed C1 to random numbers and it worked for some of , but not
all. Not sure where the formula error is

"Brad" wrote:

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.
--
Wag more, bark less


"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

What number did this not work for??
--
Wag more, bark less


"Evan" wrote:

Gary -- Thanks. It worked.

Brad, I changed C1 to random numbers and it worked for some of , but not
all. Not sure where the formula error is

"Brad" wrote:

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.
--
Wag more, bark less


"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