 |
 | 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 / January 2008NESTED IF STATEMENT
Hello, I have to calculate commission on sales tax collected based on the following criteria: 1) If sales tax collected is $11.00 or less, then the commission = to the sales tax collected 2) If sales tax collected is $11.01 - $333.33, then the commission= $11.00 3) If sales tax collected is more than $333.33, then commission = 3.3% of the sales tax collected, with a maximum amount = $99.00 My attempt was as follows, which work, however, I want to find out a better way, if any, to do this: =IF(A2<=11,A2,IF(A2<=333.33,11,IF(A2<3000,A2*0.033,99))) Thanks SJ Based on what you said??? =IF(a2>333.33,99,IF(a2>11,11,a2))  SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com Hello, I have to calculate commission on sales tax collected based on the following criteria: 1) If sales tax collected is $11.00 or less, then the commission = to the sales tax collected 2) If sales tax collected is $11.01 - $333.33, then the commission= $11.00 3) If sales tax collected is more than $333.33, then commission = 3.3% of the sales tax collected, with a maximum amount = $99.00 My attempt was as follows, which work, however, I want to find out a better way, if any, to do this: =IF(A2<=11,A2,IF(A2<=333.33,11,IF(A2<3000,A2*0.033,99))) Thanks SJ Don thanks for your response. There is a cap on the criteria # 3. In other words, if A2 is greater than $333.33 then A2 needs to be multiplied by 3.3%, however, the result of this multiplication cannot exceed $99. For example, if A2 = $3500, then 3.3% of 3500 is equal to $115.50, however, due to the capping, the commission will not be $115.50 but instead it will be $99.00 Regards SJ Based on what you said??? =IF(a2>333.33,99,IF(a2>11,11,a2)) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "SSJ" <jameel68@yahoo.com> wrote in message news:e2EvG8GWIHA.6044@TK2MSFTNGP05.phx.gbl... Hello, I have to calculate commission on sales tax collected based on the following criteria: 1) If sales tax collected is $11.00 or less, then the commission = to the sales tax collected 2) If sales tax collected is $11.01 - $333.33, then the commission= $11.00 3) If sales tax collected is more than $333.33, then commission = 3.3% of the sales tax collected, with a maximum amount = $99.00 My attempt was as follows, which work, however, I want to find out a better way, if any, to do this: =IF(A2<=11,A2,IF(A2<=333.33,11,IF(A2<3000,A2*0.033,99))) Thanks SJ =IF(J13>333.33,MIN(99,0.033*J13),IF(J13>11,11,J13)) SignatureDon Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com Don thanks for your response. There is a cap on the criteria # 3. In other words, if A2 is greater than $333.33 then A2 needs to be multiplied by 3.3%, however, the result of this multiplication cannot exceed $99. For example, if A2 = $3500, then 3.3% of 3500 is equal to $115.50, however, due to the capping, the commission will not be $115.50 but instead it will be $99.00 Regards SJ "Don Guillett" <dguillett1@austin.rr.com> wrote in message news:ekAsREHWIHA.2000@TK2MSFTNGP05.phx.gbl... Based on what you said??? =IF(a2>333.33,99,IF(a2>11,11,a2)) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "SSJ" <jameel68@yahoo.com> wrote in message news:e2EvG8GWIHA.6044@TK2MSFTNGP05.phx.gbl... Hello, I have to calculate commission on sales tax collected based on the following criteria: 1) If sales tax collected is $11.00 or less, then the commission = to the sales tax collected 2) If sales tax collected is $11.01 - $333.33, then the commission= $11.00 3) If sales tax collected is more than $333.33, then commission = 3.3% of the sales tax collected, with a maximum amount = $99.00 My attempt was as follows, which work, however, I want to find out a better way, if any, to do this: =IF(A2<=11,A2,IF(A2<=333.33,11,IF(A2<3000,A2*0.033,99))) Thanks SJ =IF(A2<=11,A2,IF(A2<=333.33,11,MIN(A2*3.3%,99))) SignatureDavid Biddulph Hello, I have to calculate commission on sales tax collected based on the following criteria: 1) If sales tax collected is $11.00 or less, then the commission = to the sales tax collected 2) If sales tax collected is $11.01 - $333.33, then the commission= $11.00 3) If sales tax collected is more than $333.33, then commission = 3.3% of the sales tax collected, with a maximum amount = $99.00 My attempt was as follows, which work, however, I want to find out a better way, if any, to do this: =IF(A2<=11,A2,IF(A2<=333.33,11,IF(A2<3000,A2*0.033,99))) Thanks SJ |
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.