greater than but less than operators for VBA
I am new to working with VBA and I need some help. I am trying to
create a commission structure based on sales ranges - here is what I have got - not sure how to write if its less than one amount but greater than another (or vice versa) any help is greatly appreciated.... Function clientcomm(sales) As Single 'calculates sales commissions based on client mgmt fees Const comm1 As Double = 0.04 Const comm2 As Double = 0.05 Const comm3 As Double = 0.06 Select Case sales Case Is = 5000000 clientcomm = sales * tier3 Case Is = 300000 <= 4999999 clientcomm = sales * tier2 Case Is < 2999999 clientcomm = sales * tier1 End Select End Function |
greater than but less than operators for VBA
If you're careful, you can arrange your cases in nice order:
Option Explicit Function clientcomm(sales as double) As Double 'calculates sales commissions based on client mgmt fees Const comm1 As Double = 0.04 Const comm2 As Double = 0.05 Const comm3 As Double = 0.06 Select Case sales Case Is < 300000 clientcomm = sales * comm1 Case Is < 500000 clientcomm = sales * comm2 Case Else clientcomm = sales * comm3 End Select End Function "Select case" will use the first one that fits and then drop through to the next line. And watch your variables (comm* vs tier*) and why not just make the function use doubles. jsr wrote: I am new to working with VBA and I need some help. I am trying to create a commission structure based on sales ranges - here is what I have got - not sure how to write if its less than one amount but greater than another (or vice versa) any help is greatly appreciated.... Function clientcomm(sales) As Single 'calculates sales commissions based on client mgmt fees Const comm1 As Double = 0.04 Const comm2 As Double = 0.05 Const comm3 As Double = 0.06 Select Case sales Case Is = 5000000 clientcomm = sales * tier3 Case Is = 300000 <= 4999999 clientcomm = sales * tier2 Case Is < 2999999 clientcomm = sales * tier1 End Select End Function -- Dave Peterson |
greater than but less than operators for VBA
Make sure you want to apply the commission to the entire amount and not
just the marginal amount. For example, let's say your commission structure is < $1,000,000 = 1% commission $1,000,000-$2,000,000 = 2% commission $3,000,000 = 3% commission In the formula you're doing, if the salesman sold $999,999, his commission would be $9,999.99. But if he sold $1,000,001, his commission would be $20,000.00. Two extra dollars in sales brought $10,000.01 in commissions!!!!! The way this should be done is up to $1,000,000 is 1%, every dollar AFTER $1,000,000 is 2%, etc. That way $1,000,001 in sales would give $1,000,000 x 1% + $1 x 2%, for a total of $10,000.02. That said, I've seen this commission structure many, many times, where one additional dollar in sales bumps up your commission percentage ALL THE WAY BACK TO ZERO. I'm amazed companies do something so illogical, but oh well. Also, this can e done with a table & formulas instead of having to use VBA. You're destroying an anthill with a rocket launcher here.... |
greater than but less than operators for VBA
Yeah, but rocket launchers are a helluva lot more fun!!! Sorry for the offpost, but I could not resist! -- bgeier ------------------------------------------------------------------------ bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822 View this thread: http://www.excelforum.com/showthread...hreadid=546093 |
greater than but less than operators for VBA
... not sure how to write if its less than one amount but
greater than another... Case Is = 300000 <= 4999999 Hi. You have excellent solutions. For future reference, I believe you were looking for this: Case 300000 To 5000000 -- HTH. :) Dana DeLouis Windows XP, Office 2003 "jsr" wrote in message ups.com... I am new to working with VBA and I need some help. I am trying to create a commission structure based on sales ranges - here is what I have got - not sure how to write if its less than one amount but greater than another (or vice versa) any help is greatly appreciated.... Function clientcomm(sales) As Single 'calculates sales commissions based on client mgmt fees Const comm1 As Double = 0.04 Const comm2 As Double = 0.05 Const comm3 As Double = 0.06 Select Case sales Case Is = 5000000 clientcomm = sales * tier3 Case Is = 300000 <= 4999999 clientcomm = sales * tier2 Case Is < 2999999 clientcomm = sales * tier1 End Select End Function |
greater than but less than operators for VBA
Thanks - it worked, I changed it a bit - per to
deal with incremental dollars.... Thanks everyone!!!!! so glad I found this forum as I am teaching myself VBA - good to have some real time guidance and help |
All times are GMT +1. The time now is 06:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com