|
Complex IF
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 |
Complex IF
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 |
Complex IF
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 |
Complex IF
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 |
Complex IF
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 |
Complex IF
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 |
Complex IF
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 |
| All times are GMT +1. The time now is 08:35 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com