User defined function not returning desired values

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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,564
Office Version
365
Platform
Windows
You aren't setting the value the UDF should return.

To do that add this before End Function.
Code:
ICRate = Rate
 

Hobolord

Board Regular
Joined
Sep 9, 2015
Messages
64
Thanks Norie.

I added that, but it still is returning all 0's for any value for Margin. Any other ideas what is wrong?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,564
Office Version
365
Platform
Windows
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)
 

Hobolord

Board Regular
Joined
Sep 9, 2015
Messages
64
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
 

Hobolord

Board Regular
Joined
Sep 9, 2015
Messages
64
Ah, thanks Sericom. One more question. My '<> "Service"' statement near the bottom doesn't seem to be working. Any ideas why?
 

sericom

Well-known Member
Joined
Jan 19, 2006
Messages
905
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,775
Messages
5,470,709
Members
406,718
Latest member
waseem11

This Week's Hot Topics

Top