Good evening all,
After a full week of searching and trying on my own I give up!
I know what I want but I can't write it, it's very frustrating!!

If anyone has some time to help me it would be appreciated.

Below is my commission model. I want to be able to add an if then
statement to it. The catch is, I want the if then statement to look at
an offset cell besides the active function cell and if it has a number
(discount) in it then apply, if not carry on as normal.

Public Tier1 As Double
Public Tier2 As Double
Public Tier3 As Double
Public Tier4 As Double
Public Tier5 As Double

Sub SetTier()
Tier1 = Worksheets("Fee schedule").Range("D3").Value
Tier2 = Worksheets("Fee schedule").Range("E3").Value
Tier3 = Worksheets("Fee schedule").Range("F3").Value
Tier4 = Worksheets("Fee schedule").Range("G3").Value
Tier5 = Worksheets("Fee schedule").Range("H3").Value
End Sub

Function fee(Assets)
' calculates annual management fee
SetTier
Select Case Assets
Case 1 To 499999.99
fee = Assets * Tier1
Case 500000 To 999999.99
fee = 500000 * Tier1 + (Assets - 500000) * Tier2
Case 1000000 To 1999999.99
fee = 500000 * Tier1 + 500000 * Tier2 + (Assets - 1000000) *
Tier3
Case 2000000 To 5000000
fee = 500000 * Tier1 + 500000 * Tier2 + 1000000 * Tier3 + _
(Assets - 2000000) * Tier4
Case Is = 5000000
fee = 500000 * Tier1 + 500000 * Tier2 + 1000000 * Tier3 + _
3000000 * Tier4 + (Assets - 5000000) * Tier5
End Select
End Function

The if statement will allow me to have a variable fee since I have more
than one client and they can all have different fee's.

If I have not made myself clear or if you think there is a better way of
doing this please say so.

Thanks again.

RTB


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!