User defined function not returning desired values

Hobolord

Hobolord

Board Regular
Joined
Sep 9, 2015
Messages
64
Hello,

I have a PC, Windows 7, and Excel 2013.

This is my first time working with a UDF, and I'm sure I've done something wrong... but I have no clue what. The function is supposed to take 3 cells, Year, Job Type, and Margin, and based on the three criteria, the output should be the proper IC Rate. However, the Rate is coming up as "0" no matter what I change the margin amount to. Below is my code:



Function ICRate(Year As Integer, JobType As String, Margin As Double) As Double



Dim Rate As Double


Select Case Year
Case Is = 2015
If JobType = "Controls" Then
If Margin < 0 Then
Rate = 0
ElseIf Margin < 0.26 Then
Rate = 0.075
ElseIf Margin < 0.28 Then
Rate = 0.08
ElseIf Margin < 0.3 Then
Rate = 0.085
ElseIf Margin < 0.32 Then
Rate = 0.09
ElseIf Margin < 0.34 Then
Rate = 0.095
Else
Rate = 0.1
End If
End If

If JobType = "Service" Then
If Margin < 0 Then
Rate = 0
ElseIf Margin < 0.16 Then
Rate = 0.015
ElseIf Margin < 0.18 Then
Rate = 0.017
ElseIf Margin < 0.2 Then
Rate = 0.019
ElseIf Margin < 0.22 Then
Rate = 0.021
ElseIf Margin < 0.24 Then
Rate = 0.023
ElseIf Margin < 0.26 Then
Rate = 0.025
ElseIf Margin < 0.28 Then
Rate = 0.027
ElseIf Margin < 0.3 Then
Rate = 0.029
ElseIf Margin < 0.32 Then
Rate = 0.031
ElseIf Margin < 0.34 Then
Rate = 0.033
ElseIf Margin < 0.36 Then
Rate = 0.035
ElseIf Margin < 0.38 Then
Rate = 0.037
ElseIf Margin < 0.4 Then
Rate = 0.039
ElseIf Margin < 0.42 Then
Rate = 0.041
ElseIf Margin < 0.44 Then
Rate = 0.043
ElseIf Margin < 0.46 Then
Rate = 0.045
Else
Rate = 0.047
End If
End If
End Select
End Function

Any ideas?

Thank you!!

Hobo
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
You aren't setting the value the UDF should return.

To do that add this before End Function.
Code: 
ICRate = Rate
 
Upvote 0
Thanks Norie.

I added that, but it still is returning all 0's for any value for Margin. Any other ideas what is wrong?
 
Upvote 0
It works fine for me, where exactly did you add the code I suggested?

Here's how I have the code, which is in a standard module,
Code: 
Function ICRate(Year As Integer, JobType As String, Margin As Double) As Double
Dim Rate As Double


    Select Case Year
        Case Is = 2015
            If JobType = "Controls" Then
                If Margin < 0 Then
                    Rate = 0
                ElseIf Margin < 0.26 Then
                    Rate = 0.075
                ElseIf Margin < 0.28 Then
                    Rate = 0.08
                ElseIf Margin < 0.3 Then
                    Rate = 0.085
                ElseIf Margin < 0.32 Then
                    Rate = 0.09
                ElseIf Margin < 0.34 Then
                    Rate = 0.095
                Else
                    Rate = 0.1
                End If
            End If

            If JobType = "Service" Then
                If Margin < 0 Then
                    Rate = 0
                ElseIf Margin < 0.16 Then
                    Rate = 0.015
                ElseIf Margin < 0.18 Then
                    Rate = 0.017
                ElseIf Margin < 0.2 Then
                    Rate = 0.019
                ElseIf Margin < 0.22 Then
                    Rate = 0.021
                ElseIf Margin < 0.24 Then
                    Rate = 0.023
                ElseIf Margin < 0.26 Then
                    Rate = 0.025
                ElseIf Margin < 0.28 Then
                    Rate = 0.027
                ElseIf Margin < 0.3 Then
                    Rate = 0.029
                ElseIf Margin < 0.32 Then
                    Rate = 0.031
                ElseIf Margin < 0.34 Then
                    Rate = 0.033
                ElseIf Margin < 0.36 Then
                    Rate = 0.035
                ElseIf Margin < 0.38 Then
                    Rate = 0.037
                ElseIf Margin < 0.4 Then
                    Rate = 0.039
                ElseIf Margin < 0.42 Then
                    Rate = 0.041
                ElseIf Margin < 0.44 Then
                    Rate = 0.043
                ElseIf Margin < 0.46 Then
                    Rate = 0.045
                Else
                    Rate = 0.047
                End If
            End If
    End Select
    
    ICRate = Rate
    
End Function

and when I entered this formula in a cell it returned 0.08, which I think is right.

=ICRate(2015,"Controls",0.27)
 
Upvote 0
Interesting. It must be a problem related to my entire code then. I apologize; I assumed if I fixed a part of it, it would be fine. Below is the entire code. Would you mind looking at that?

Code: 
Function ICRate(Year As Integer, JobType As String, Margin As Double) As Double
 
Dim Rate As Double


Select Case Year
Case Is = 2015
    If JobType = "Controls" Then
        If Margin < 0 Then
        Rate = 0
        ElseIf Margin < 0.26 Then
        Rate = 0.075
        ElseIf Margin < 0.28 Then
        Rate = 0.08
        ElseIf Margin < 0.3 Then
        Rate = 0.085
        ElseIf Margin < 0.32 Then
        Rate = 0.09
        ElseIf Margin < 0.34 Then
        Rate = 0.095
        Else
        Rate = 0.1
        End If
    End If
    
    If JobType = "Service" Then
        If Margin < 0 Then
        Rate = 0
        ElseIf Margin < 0.16 Then
        Rate = 0.015
        ElseIf Margin < 0.18 Then
        Rate = 0.017
        ElseIf Margin < 0.2 Then
        Rate = 0.019
        ElseIf Margin < 0.22 Then
        Rate = 0.021
        ElseIf Margin < 0.24 Then
        Rate = 0.023
        ElseIf Margin < 0.26 Then
        Rate = 0.025
        ElseIf Margin < 0.28 Then
        Rate = 0.027
        ElseIf Margin < 0.3 Then
        Rate = 0.029
        ElseIf Margin < 0.32 Then
        Rate = 0.031
        ElseIf Margin < 0.34 Then
        Rate = 0.033
        ElseIf Margin < 0.36 Then
        Rate = 0.035
        ElseIf Margin < 0.38 Then
        Rate = 0.037
        ElseIf Margin < 0.4 Then
        Rate = 0.039
        ElseIf Margin < 0.42 Then
        Rate = 0.041
        ElseIf Margin < 0.44 Then
        Rate = 0.043
        ElseIf Margin < 0.46 Then
        Rate = 0.045
        Else
        Rate = 0.047
        End If
    End If
    
    If JobType = "Labor" Then
        If Margin < 0 Then
        Rate = 0
        ElseIf Margin < 0.16 Then
        Rate = 0.01
        ElseIf Margin < 0.18 Then
        Rate = 0.012
        ElseIf Margin < 0.2 Then
        Rate = 0.014
        ElseIf Margin < 0.22 Then
        Rate = 0.016
        ElseIf Margin < 0.24 Then
        Rate = 0.018
        ElseIf Margin < 0.26 Then
        Rate = 0.02
        ElseIf Margin < 0.28 Then
        Rate = 0.022
        ElseIf Margin < 0.3 Then
        Rate = 0.024
        ElseIf Margin < 0.32 Then
        Rate = 0.026
        ElseIf Margin < 0.34 Then
        Rate = 0.028
        ElseIf Margin < 0.36 Then
        Rate = 0.03
        ElseIf Margin < 0.38 Then
        Rate = 0.032
        ElseIf Margin < 0.4 Then
        Rate = 0.034
        ElseIf Margin < 0.42 Then
        Rate = 0.036
        ElseIf Margin < 0.44 Then
        Rate = 0.038
        ElseIf Margin < 0.46 Then
        Rate = 0.04
        Else
        Rate = 0.042
        End If
    End If
    
    If JobType = "Mechanical" Then
        If Margin < 0 Then
        Rate = 0
        ElseIf Margin < 0.18 Then
        Rate = 0.065
        ElseIf Margin < 0.2 Then
        Rate = 0.07
        ElseIf Margin < 0.22 Then
        Rate = 0.075
        ElseIf Margin < 0.24 Then
        Rate = 0.08
        ElseIf Margin < 0.26 Then
        Rate = 0.085
        ElseIf Margin < 0.28 Then
        Rate = 0.09
        ElseIf Margin < 0.3 Then
        Rate = 0.095
        ElseIf Margin < 0.32 Then
        Rate = 0.1
        ElseIf Margin < 0.34 Then
        Rate = 0.11
        ElseIf Margin < 0.36 Then
        Rate = 0.12
        ElseIf Margin < 0.38 Then
        Rate = 0.13
        ElseIf Margin < 0.4 Then
        Rate = 0.14
        ElseIf Margin < 0.42 Then
        Rate = 0.15
        ElseIf Margin < 0.44 Then
        Rate = 0.16
        Else
        Rate = 0.17
        End If
    End If


Case Is = 2014
    If JobType = "Controls" Then
        If Margin <= 0 Then
        Rate = 0
        ElseIf Margin = 0 Then
        Rate = 0
        ElseIf Margin < 0.26 Then
        Rate = 0.075
        ElseIf Margin < 0.28 Then
        Rate = 0.08
        ElseIf Margin < 0.3 Then
        Rate = 0.085
        ElseIf Margin < 0.32 Then
        Rate = 0.09
        ElseIf Margin < 0.34 Then
        Rate = 0.095
        Else
        Rate = 0.1
        End If
    End If
    
    If JobType = "Service" Then
        If Margin <= 0 Then
        Rate = 0
        ElseIf Margin = 0 Then
        Rate = 0
        ElseIf Margin < 0.22 Then
        Rate = 0.08
        ElseIf Margin < 0.24 Then
        Rate = 0.09
        ElseIf Margin < 0.26 Then
        Rate = 0.1
        ElseIf Margin < 0.28 Then
        Rate = 0.11
        ElseIf Margin < 0.3 Then
        Rate = 0.12
        ElseIf Margin < 0.32 Then
        Rate = 0.13
        ElseIf Margin < 0.34 Then
        Rate = 0.14
        ElseIf Margin < 0.36 Then
        Rate = 0.15
        ElseIf Margin < 0.38 Then
        Rate = 0.16
        ElseIf Margin < 0.4 Then
        Rate = 0.18
        ElseIf Margin < 0.42 Then
        Rate = 0.2
        ElseIf Margin < 0.44 Then
        Rate = 0.22
        ElseIf Margin < 0.46 Then
        Rate = 0.24
        ElseIf Margin < 0.48 Then
        Rate = 0.26
        Else
        Rate = 0.28
        End If
    End If
    
    If JobType = "Labor" Then
        If Margin < 0 Then
        Rate = 0
        ElseIf Margin < 0.22 Then
        Rate = 0.04
        ElseIf Margin < 0.24 Then
        Rate = 0.045
        ElseIf Margin < 0.26 Then
        Rate = 0.05
        ElseIf Margin < 0.28 Then
        Rate = 0.055
        ElseIf Margin < 0.3 Then
        Rate = 0.06
        ElseIf Margin < 0.32 Then
        Rate = 0.065
        ElseIf Margin < 0.34 Then
        Rate = 0.07
        ElseIf Margin < 0.36 Then
        Rate = 0.075
        ElseIf Margin < 0.38 Then
        Rate = 0.08
        ElseIf Margin < 0.4 Then
        Rate = 0.09
        ElseIf Margin < 0.42 Then
        Rate = 0.1
        ElseIf Margin < 0.44 Then
        Rate = 0.11
        ElseIf Margin < 0.46 Then
        Rate = 0.12
        ElseIf Margin < 0.48 Then
        Rate = 0.13
        Else
        Rate = 0.14
        End If
    End If
    
    If JobType = "Mechanical" Then
        If Margin < 0 Then
        Rate = 0
            If Margin = 0 Then
            Rate = 0
            ElseIf Margin < 0.18 Then
            Rate = 0.065
            ElseIf Margin < 0.2 Then
            Rate = 0.07
            ElseIf Margin < 0.22 Then
            Rate = 0.075
            ElseIf Margin < 0.24 Then
            Rate = 0.08
            ElseIf Margin < 0.26 Then
            Rate = 0.085
            ElseIf Margin < 0.28 Then
            Rate = 0.09
            ElseIf Margin < 0.3 Then
            Rate = 0.095
            ElseIf Margin < 0.32 Then
            Rate = 0.1
            ElseIf Margin < 0.34 Then
            Rate = 0.11
            ElseIf Margin < 0.36 Then
            Rate = 0.12
            ElseIf Margin < 0.38 Then
            Rate = 0.13
            ElseIf Margin < 0.4 Then
            Rate = 0.14
            ElseIf Margin < 0.42 Then
            Rate = 0.15
            ElseIf Margin < 0.44 Then
            Rate = 0.16
            Else
            Rate = 0.17
            End If
        End If
    End If


Case Is = 2016
    If JobType = "Service" Then
        If Margin <= 0 Then
        Rate = 0
        ElseIf Margin < 0.14 Then
        Rate = 0.015
        ElseIf Margin < 0.16 Then
        Rate = 0.017
        ElseIf Margin < 0.18 Then
        Rate = 0.019
        ElseIf Margin < 0.2 Then
        Rate = 0.021
        ElseIf Margin < 0.22 Then
        Rate = 0.023
        ElseIf Margin < 0.24 Then
        Rate = 0.025
        ElseIf Margin < 0.26 Then
        Rate = 0.027
        ElseIf Margin < 0.28 Then
        Rate = 0.029
        ElseIf Margin < 0.3 Then
        Rate = 0.031
        ElseIf Margin < 0.32 Then
        Rate = 0.033
        ElseIf Margin < 0.34 Then
        Rate = 0.035
        ElseIf Margin < 0.36 Then
        Rate = 0.037
        ElseIf Margin < 0.38 Then
        Rate = 0.039
        ElseIf Margin < 0.4 Then
        Rate = 0.41
        ElseIf Margin < 0.42 Then
        Rate = 0.043
        ElseIf Margin < 0.44 Then
        Rate = 0.045
        ElseIf Margin < 0.46 Then
        Rate = 0.047
        Else
        Rate = 0.047
        End If
    End If
    If JobType <> "Service" Then
        If Margin < 0 Then
        Rate = 0
            If Margin = 0 Then
            Rate = 0
                If Margin < 0.14 Then
                Rate = 0.012
                ElseIf Margin < 0.16 Then
                Rate = 0.014
                ElseIf Margin < 0.18 Then
                Rate = 0.016
                ElseIf Margin < 0.2 Then
                Rate = 0.018
                ElseIf Margin < 0.22 Then
                Rate = 0.02
                ElseIf Margin < 0.24 Then
                Rate = 0.022
                ElseIf Margin < 0.26 Then
                Rate = 0.024
                ElseIf Margin < 0.28 Then
                Rate = 0.026
                ElseIf Margin < 0.3 Then
                Rate = 0.028
                ElseIf Margin < 0.32 Then
                Rate = 0.03
                ElseIf Margin < 0.34 Then
                Rate = 0.032
                ElseIf Margin < 0.36 Then
                Rate = 0.034
                ElseIf Margin < 0.38 Then
                Rate = 0.036
                ElseIf Margin < 0.4 Then
                Rate = 0.38
                ElseIf Margin < 0.42 Then
                Rate = 0.04
                ElseIf Margin < 0.44 Then
                Rate = 0.042
                ElseIf Margin < 0.46 Then
                Rate = 0.044
                Else
                Rate = 0.044
                End If
            End If
        End If
    End If


ICRate = Rate




End Select
End Function
 
Upvote 0
Ah, thanks Sericom. One more question. My '<> "Service"' statement near the bottom doesn't seem to be working. Any ideas why?
 
Upvote 0
Try this, I rearranged your 2016 Case

Code: 
Case Is = 2016
    If JobType = "Service" Then
        If Margin <= 0 Then
            Rate = 0
        ElseIf Margin < 0.14 Then
            Rate = 0.015
        ElseIf Margin < 0.16 Then
            Rate = 0.017
        ElseIf Margin < 0.18 Then
            Rate = 0.019
        ElseIf Margin < 0.2 Then
            Rate = 0.021
        ElseIf Margin < 0.22 Then
            Rate = 0.023
        ElseIf Margin < 0.24 Then
            Rate = 0.025
        ElseIf Margin < 0.26 Then
            Rate = 0.027
        ElseIf Margin < 0.28 Then
            Rate = 0.029
        ElseIf Margin < 0.3 Then
            Rate = 0.031
        ElseIf Margin < 0.32 Then
            Rate = 0.033
        ElseIf Margin < 0.34 Then
            Rate = 0.035
        ElseIf Margin < 0.36 Then
            Rate = 0.037
        ElseIf Margin < 0.38 Then
            Rate = 0.039
        ElseIf Margin < 0.4 Then
            Rate = 0.41
        ElseIf Margin < 0.42 Then
            Rate = 0.043
        ElseIf Margin < 0.44 Then
            Rate = 0.045
        ElseIf Margin < 0.46 Then
            Rate = 0.047
        Else
            Rate = 0.047
        End If
    Else
        If Margin <= 0 Then
            Rate = 0
        Else
            If Margin < 0.14 Then
                Rate = 0.012
            ElseIf Margin < 0.16 Then
                Rate = 0.014
            ElseIf Margin < 0.18 Then
                Rate = 0.016
            ElseIf Margin < 0.2 Then
                Rate = 0.018
            ElseIf Margin < 0.22 Then
                Rate = 0.02
            ElseIf Margin < 0.24 Then
                Rate = 0.022
            ElseIf Margin < 0.26 Then
                Rate = 0.024
            ElseIf Margin < 0.28 Then
                Rate = 0.026
            ElseIf Margin < 0.3 Then
                Rate = 0.028
            ElseIf Margin < 0.32 Then
                Rate = 0.03
            ElseIf Margin < 0.34 Then
                Rate = 0.032
            ElseIf Margin < 0.36 Then
                Rate = 0.034
            ElseIf Margin < 0.38 Then
                Rate = 0.036
            ElseIf Margin < 0.4 Then
                Rate = 0.38
            ElseIf Margin < 0.42 Then
                Rate = 0.04
            ElseIf Margin < 0.44 Then
                Rate = 0.042
            ElseIf Margin < 0.46 Then
                Rate = 0.044
            Else
                Rate = 0.044
            End If
        End If
    End If
End Select
ICRate = Rate
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
formula to return two columns of data adjacent to #N/A cell entry
Replies
3
Views
219
Fluff
Fluff
I
  • Solved
Lookup nth column value for specified 'n'
2
Replies
19
Views
860
Peter_SSs
Peter_SSs
S
  • Question
User Defined Functions: working with function output
2
Replies
13
Views
2K
SBF12345
S
P
  • Question
User Defined function provides #value error
2
Replies
14
Views
845
P_Schmitz
P
L
  • Question
Returning Multiple Values Through A Vlookup - Formula or VBA
Replies
0
Views
2K
liamlarmour
L

Forum statistics

Threads
1,214,411
Messages
6,119,356
Members
448,888
Latest member
Arle8907

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top