Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Are there Boolean operators in MS Excel? | Excel Discussion (Misc queries) | |||
SumIf: use of greater or less than operators in Criteria | Excel Worksheet Functions | |||
Where are Bitwise Operators | Excel Worksheet Functions | |||
Operators With Times | Excel Discussion (Misc queries) | |||
Unary operators | Excel Worksheet Functions |