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,

 

[CWatts]

To use max and min you need WorksheetFunction.Max or WorksheetFunction.Min. Also your function name is always your return value so ditch
SB1Commission = Tier1 + Tier2 + Tier3 + Tier4
and make it
SB1Calculation = Tier1 + Tier2 + Tier3 + Tier4

Or if you have to keep the SB1Commission variable for some reason, add another line before the end of the function with

SB1Calculation = SB1Commission

 

[sfsteve002]

Hi, I made the changes that you noted, however, I am still getting #Value! as as result.

 

[CWatts]

Highlight the cell with the custom function, push F2 to edit and then push enter to force it to recalculate.

Could you repost your code so I know what you're now working with? (Within CODE blocks please)

 

[sfsteve002]

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

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 = Worksheet.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 = Worksheet.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

I'm running Mac Office 2011 so I was unable to use the F2 button to recalculated, but I clicked on the check button and it recalculated and still the same error though.

 

[CWatts]

You're still using the wrong syntax, and PLEASE put your code between CODE tags, that prevents the text from wrapping and it makes it easier to debug. You just click on the # picture when you're entering your message. and get:

Put your code between the

 and the

To use max and min you need WorksheetFunction.Max

 

[sfsteve002]

I'm not sure if I understand you correctly. Are you saying that I should replace most of my parentheses () symbols with brackets [] instead?

 

[SuperFerret]

What CWatts is saying is post your full code to the board, but follow these steps:
1. Paste your code into the new reply box
2. Highlight all your code in the 'post new reply'
3. Click on the button marked #
4. Submit reply

It'll show up like this:

Example

This makes it easier to spot errors as it will show up just as it does in the VBA window

 

[CWatts]

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

But the bigger problem is you didn't implement one of the fixes correctly. You put "Worksheet" when you need to put "WorksheetFunction" like the example in red.

 

[sfsteve002]

How about this below?


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

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 = Worksheet.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 = Worksheet.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