Help Creating Custom Function

[sfsteve002]

Hi, I'm trying to create the custom function below, but it does not work. I can't seem to figure out what I'm doing wrong. Can someone help?


Function SB1Calculation(Actual, Goal, OTV)

Dim Tier1 As Integer
Dim Tier2 As Integer
Dim Tier3 As Integer
Dim Tier4 As Integer
Dim SB1Calculation As Integer

Tier1 = (0.75 * OTV) / Goal

If Actual >= Goal Then
Tier2 = Max(Min(Actual - Goal, Goal * 1.15), 0) * (2 * ((0.75 * OTV) / Goal))
Else
Tier2 = 0
End If

If Actual >= Goal * 1.15 Then
Tier3 = Max(Min(((Actual - Goal) * 1.15), Goal * 0.15), 0) * (4 * ((0.75 * OTV) / Goal))
Else
Tier3 = 0
End If

If Actual > (2 * Goal) Then
Tier4 = (Actual - (2 * Goal)) * (0.75 * (OTV / Goal))
Else
Tier4 = 0
End If

SB1Commission = Tier1 + Tier2 + Tier3 + Tier4

End Function

Thanks,

 

[SuperFerret]

Than's SF, I was unsure how I was going to reply to that.

Quite alright

 

[sfsteve002]

Function SB1Calculation(Actual As Currency, Goal As Currency, OTV As Currency) As Currency

Dim Tier1 As Integer
Dim Tier2 As Integer
Dim Tier3 As Integer
Dim Tier4 As Integer

    Tier1 = [0.75 * OTV] / [Goal]

If Actual >= Goal Then
    Tier2 = WorksheetFunction.Max(Worksheet.Min([Actual - Goal], [Goal * 1.15]), 0) * [2 * [[0.75 * OTV] / [Goal]]]
    Else
    Tier2 = 0
End If

If Actual >= Goal * 1.15 Then
    Tier3 = WorksheetFunction.Max(Worksheet.Min([Actual] - [Goal * 1.15], [Goal * 2] - [Goal * 1.15]), 0) * [4 * [[0.75 * OTV] / [Goal]]]
    Else
    Tier3 = 0
End If

If Actual > (2 * Goal) Then
    Tier4 = [Actual - [2 * Goal]] * [0.75 * [OTV / Goal]]
    Else
    Tier4 = 0
End If

SB1Calculation = Tier1 + Tier2 + Tier3 + Tier4

End Function

 

[SuperFerret]

I think you still need WorksheetFunction on Min as well:

WorksheetFunction.Max(Worksheet[B][COLOR=red]Function[/COLOR][/B].Min

 

[sfsteve002]

Still getting the #Value! error after the changes.

Function SB1Calculation(Actual As Currency, Goal As Currency, OTV As Currency) As Currency

Dim Tier1 As Integer
Dim Tier2 As Integer
Dim Tier3 As Integer
Dim Tier4 As Integer

    Tier1 = [0.75 * OTV] / [Goal]

If Actual >= Goal Then
    Tier2 = WorksheetFunction.Max(WorksheetFunction.Min([Actual - Goal], [Goal * 1.15]), 0) * [2 * [[0.75 * OTV] / [Goal]]]
    Else
    Tier2 = 0
End If

If Actual >= Goal * 1.15 Then
    Tier3 = WorksheetFunction.Max(WorksheetFunction.Min([Actual] - [Goal * 1.15], [Goal * 2] - [Goal * 1.15]), 0) * [4 * [[0.75 * OTV] / [Goal]]]
    Else
    Tier3 = 0
End If

If Actual > (2 * Goal) Then
    Tier4 = [Actual - [2 * Goal]] * [0.75 * [OTV / Goal]]
    Else
    Tier4 = 0
End If

SB1Calculation = Tier1 + Tier2 + Tier3 + Tier4

End Function

 

[CWatts]

Just use this:

Function SB1Calculation(Actual As Currency, Goal As Currency, OTV As Currency)
 
Dim Tier1 As Integer
Dim Tier2 As Integer
Dim Tier3 As Integer
Dim Tier4 As Integer
 
Tier1 = (0.75 * OTV) / Goal
 
If Actual >= Goal Then Tier2 = WorksheetFunction.Max(WorksheetFunction.Min(Actual - Goal, Goal * 1.15), 0) * (2 * ((0.75 * OTV) / Goal)) Else Tier2 = 0
If Actual >= Goal * 1.15 Then Tier3 = WorksheetFunction.Max(WorksheetFunction.Min(((Actual - Goal) * 1.15), Goal * 0.15), 0) * (4 * ((0.75 * OTV) / Goal)) Else Tier3 = 0
If Actual > (2 * Goal) Then Tier4 = (Actual - (2 * Goal)) * (0.75 * (OTV / Goal)) Else Tier4 = 0
 
SB1Calculation = Tier1 + Tier2 + Tier3 + Tier4
 
End Function

 

[sfsteve002]

I just tried it and I still getting the #Value! error.

Function SB3Calculation(Actual As Currency, Goal As Currency, OTV As Currency)
 
Dim Tier1 As Integer
Dim Tier2 As Integer
Dim Tier3 As Integer
Dim Tier4 As Integer
 
Tier1 = (0.75 * OTV) / Goal
 
If Actual >= Goal Then Tier2 = WorksheetFunction.Max(WorksheetFunction.Min(Actual - Goal, Goal * 1.15), 0) * (2 * ((0.75 * OTV) / Goal)) Else Tier2 = 0
If Actual >= Goal * 1.15 Then Tier3 = WorksheetFunction.Max(WorksheetFunction.Min(((Actual - Goal) * 1.15), Goal * 0.15), 0) * (4 * ((0.75 * OTV) / Goal)) Else Tier3 = 0
If Actual > (2 * Goal) Then Tier4 = (Actual - (2 * Goal)) * (0.75 * (OTV / Goal)) Else Tier4 = 0
 
SB3Calculation = Tier1 + Tier2 + Tier3 + Tier4
 
End Function

 

[gsistek]

Try with Dim Tier1, Tier2, etc. as Double instead of Integer.

 

[sfsteve002]

It worked!!!!! Thanks, everyone! Great work!!!