Function Help

  • Thread starter Thread starter Gordon Chapman
  • Start date Start date
G

Gordon Chapman

Have a column of values, that need to pay a commission on but this is scaled
so that

1-50 pay 0.5
51-4000 pay 0.4
4001 pay 0.3

I.E. if value where 4500, commision due would be 1754


How is this best done is automatically

Thanks

GC
 
Gordon;

Try this one as an example and modify it yourself to excercise.
Copy the text into a module in VBA.

Then in a cel in your worksheet, type
=COMMISSION2(B2;C2),
Where B2 holds the amount of sales and C2 the number of years an employe is
present.

Function COMMISSION2(Sales, Years) As Single
' Calculates sales commissions based on
' years in service
Const Tier1 As Double = 0.5
Const Tier2 As Double = 0.4
Const Tier3 As Double = 0.3
Const Tier4 As Double = 0.2
Select Case Sales
Case 0 To 50
COMMISSION2 = Sales * Tier1
Case 51 To 4000
COMMISSION2 = Sales * Tier2
Case 4001 To 10000
COMMISSION2 = Sales * Tier3
Case Is >= 10000
COMMISSION2 = Sales * Tier4
End Select
COMMISSION2 = COMMISSION2 + (COMMISSION2 * Years / 100)
End Function

Mark.
 
Back
Top