MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Commission code


Posted by Chris on April 02, 2001 8:48 AM

I am writing a function that will calculate the commission payable to an employee if a certain sales target is made. following is the code and it doesnt seem to work.
Chris

Function Commission(Sale, How_Big)
'
'Calculates the commision to be paid to the employees
'
Commission_Rate1 = 0.15
Commission_Rate2 = 0.23
yes = "yes"
If Sale = "yes" Then
If Sale <= 500 Then
Commission = 0

If Sale <= 1000 Then
Commission = How_Big * Commission_Rate1

If Sale >= 1500 Then
Commission = How_Big * Commission_Rate2

Else
Commission = 0
End If
End If
End If
End If


Commission = Application.Round(Commission,2) 'Round for currency
End Function


Posted by mseyf on April 02, 2001 11:11 AM

Chris-

without knowing what 'Sale' and 'How_Big' are supposed to represent, it appears that the lines

Sale<=500
Sale<=1000
Sale>=1500

should be

How_Big<=500
How_Big<=1000
How_Big>=1500

side note: what about sales between 1000 and 1500?

HTH

Mark

Calculates the commision to be paid to the employees If Sale <= 1000 Then Commission = How_Big * Commission_Rate1 If Sale >= 1500 Then Commission = How_Big * Commission_Rate2 Else End If End If End If End If

Posted by Dave Hawley on April 02, 2001 11:38 AM

Calculates the commision to be paid to the employees If Sale <= 1000 Then Commission = How_Big * Commission_Rate1 If Sale >= 1500 Then Commission = How_Big * Commission_Rate2 Else End If End If End If End If


Chris, You have:

If Sale = "yes" Then
If Sale <= 500 Then

Is Sale a String or a Integer ?

You also need to change:
Commission = Application.Round(Commission,2) 'Round for currency

TO

Commission = Application.WorksheetFunction.Round(Commission, 2) 'Round for currency

Dave
OzGrid Business Applications

Posted by Chris on April 03, 2001 10:37 AM

thanks

Thanks guys