 |
 | 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 / New Users / September 2006Sliding Scale Percentage via IF?
Hello all, I'm trying to do a formula for a sliding scale percentage fee, for which it references the item price of an object. Ie: First $25.00 = 10%, $25.01-100 = 10% of first $25 + 7% of remaining price, $100.01-1000 = 10% of first $25, 7% of first $25.01-100, and 5% of remaining price, etc. However! The formula I cobbled together won't debug correctly - it works up to the 7% range, but trying to get that third jump to the 5% and beyond returns a value of 'FALSE'. I can see what's happening in the formula evaluator, but I'm at a loss on how to side-step it. Formula: =IF(B8<25.01,B8*0.1,IF(AND(B8>=25.01,B8<100.01),SUM(2.5,(B8-25)*0.07,IF(AND(B8>=100.01,B8<1000.01) SUM(2.5,5.25,(B8-100)*0.05),IF(B8>=1000.01,SUM(2.5,5.25,(B8-100)*0.05,(B8-1000)*0.03),))))) Broke the line, or else it would've stretched the screen quite wide. The value in B8 is $180. So when it gets to the second part of the first AND argument, it returns a FALSE and the rest of the formula is moot, and I feel sure the same would happen for items in the $1000+ range as well. But for items less than $100, so far it works. So I'd need another way of linking those two conditions together and returning the amount of the fee seperate from the initial price (in this case $11.75 if my hand-calculations are accurate). Try this...just revesed the order but it seems to work in my spreadsheet. =IF(AND(B8>=100.01,B8<1000.01),SUM(7.75,(B8-100)*0.05),IF(B8>=1000.01,SUM(2.5,5.25,45,(B8-1000)*0.03),IF(B8<25.01,B8*0.1,IF(AND(B8>=25.01,B8<100.01),SUM(2.5+(B8-25)*0.07))))) > Hello all, > [quoted text clipped - 21 lines] > from the initial price (in this case $11.75 if my hand-calculations are > accurate). That worked like a charm. Thank you! Was that the proper way to approach that particular issue, or was it more of a 'sledgehammer instead of a scalpel' method? > Try this...just revesed the order but it seems to work in my spreadsheet. > [quoted text clipped - 25 lines] > > from the initial price (in this case $11.75 if my hand-calculations are > > accurate). have to be honest...just luck!! I broke down the formula in pieces to ensure each if worked on its own then put them back together in reverse order---and it worked. Glad I could help. > That worked like a charm. Thank you! Was that the proper way to approach that > particular issue, or was it more of a 'sledgehammer instead of a scalpel' [quoted text clipped - 29 lines] > > > from the initial price (in this case $11.75 if my hand-calculations are > > > accurate). You could try this formula, which is not really a "scalpel", but it also is *not* as intuitive, although it is a bit more concise: =SUMPRODUCT((B8>{0;25.01;100.01;1000.01})*(B8-{0;25.01;100.01;1000.01})*({0.1;-0.03;-0.02;-0.02}))  SignatureHTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- > That worked like a charm. Thank you! Was that the proper way to approach > that [quoted text clipped - 40 lines] >> > from the initial price (in this case $11.75 if my hand-calculations are >> > accurate). |
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.