 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Worksheet Functions / June 2008Complex IF formula
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... =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 > 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 [quoted text clipped - 10 lines] > was $200, $450, $1600, etc.... What's the correct IF formula? Any help > would be appreciated 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 >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 [quoted text clipped - 11 lines] > was $200, $450, $1600, etc.... What's the correct IF formula? Any help > would be appreciated 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  SignatureBiff Microsoft Excel MVP >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 [quoted text clipped - 11 lines] > was $200, $450, $1600, etc.... What's the correct IF formula? Any help > would be appreciated >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 [quoted text clipped - 11 lines] >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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.