Commission calculations

Rick, try changing your function to this.

Function CalcCommission(Amount)
'This function calculates commission
Dim Level1 As Long
Dim Level2 As Long
Dim Level3 As Long
Dim Level4 As Long
Dim Rate1 As Double
Dim Rate2 As Double
Dim Rate3 As Double
Dim Rate4 As Double

'Get the commission thresholds
Level1 = CLng(InputBox("Enter Commission amount for Level 1"))
Level2 = CLng(InputBox("Enter Commission amount for Level 2"))
Level3 = CLng(InputBox("Enter Commission amount for Level 3"))
Level4 = CLng(InputBox("Enter Commission amount for Level 4"))
'Get the rates
Rate1 = CDbl(InputBox("Enter Commission rate for Level 1"))
Rate2 = CDbl(InputBox("Enter Commission rate for Level 2"))
Rate3 = CDbl(InputBox("Enter Commission rate for Level 3"))
Rate4 = CDbl(InputBox("Enter Commission rate for Level 4"))
'first calculate the Amount Commission
Select Case Amount
Case Is < Level1
AmountComm = Amount * Rate1
Case Level1 To Level2
AmountComm = Level1 * Rate1 + ((Amount - Level1) * Rate2)
Case Level2 To Level3
AmountComm = Level1 * Rate1 + ((Level2 - Level1) * Rate2) + ((Amount - Level3) * Rate3)
Case Else
AmountComm = Level1 * Rate1 + ((Level2 - Level1) * Rate2) + ((Level4 - Level3) * Rate3) + ((Amount - Level4) * Rate4)

End Select

'now return value in Function name
CalcCommission = AmountComm
End Function

Note - I did not get a chance to test this so please test to make sure. The other thing you could do is add variables to your function so the user has to input them in the formula.

Regards,
BarrieBarrie Davidson

Barrie,

That's great, the only problem being that the boxes for the variables keep popping up before i have entered the sales figure. Is there a way i can stop this from happening?

Many, many thanks

Rick

That's great, the only problem being that the boxes for the variables keep popping up before i have entered the sales figure. Is there a way i can stop this from happening? Many, many thanks Rick

Rick, how about changing your function to include all the variables (since I don't know how to stop the boxes from popping up)? Like this:

Function CalcCommission(Amount, Level1 As Long, Level2 As Long, Level3 As Long, Level4 As Long, Rate1 As Double, Rate2 As Double, Rate3 As Double, Rate4 As Double)
'This function calculates commission

'first calculate the Amount Commission
Select Case Amount
Case Is < Level1
AmountComm = Amount * Rate1
Case Level1 To Level2
AmountComm = Level1 * Rate1 + ((Amount - Level1) * Rate2)
Case Level2 To Level3
AmountComm = Level1 * Rate1 + ((Level2 - Level1) * Rate2) + ((Amount - Level3) * Rate3)
Case Else
AmountComm = Level1 * Rate1 + ((Level2 - Level1) * Rate2) + ((Level4 - Level3) * Rate3) + ((Amount - Level4) * Rate4)

End Select

'now return value in Function name
CalcCommission = AmountComm
End Function

Does this work better?
BarrieBarrie Davidson

Barrie

That works truly great.

Many thanks for your help.

Rick