MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Commission calculations


Posted by Rick on January 14, 2002 5:28 AM

If i have a vb module such as below:

Function CalcCommission(Amount)
'This function calculates commission

'first calculate the Amount Commission
Select Case Amount
Case Is < 250000
AmountComm = Amount * 0.005
Case 250000 To 500000
AmountComm = 1250 + ((Amount - 250000) * 0.01)
Case 500000 To 749999
AmountComm = 1250 + 2500 + ((Amount - 500000) * 0.02)
Case Else
AmountComm = 1250 + 2500 + 5000 + ((Amount - 750000) * 0.03)

End Select

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


How can i change this so i am asked for the various
elements i.e. commission bands and commission rates

thanks

rick


Posted by Barrie Davidson on January 14, 2002 7:40 AM

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

Posted by Rick on January 14, 2002 8:14 AM

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

Posted by Barrie Davidson on January 14, 2002 8:25 AM

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

Posted by Rick on January 14, 2002 8:53 AM

Barrie

That works truly great.

Many thanks for your help.

Rick