Hello everyone,
I wanted to converted numeric values in a column to rating scales from 0 - 10. The scheme is:
ER < -10.0% 0
-10.0% <= ER < -5.0% 1
-5.0% <= ER < -2.0% 2
-2.0% <= ER < -0.5% 3
-0.5% <= ER < -0.1% 4
-0.1% <= ER < 0.1% 5
0.1% <= ER < 0.5% 6
0.5% <= ER < 2.0% 7
2.0% <= ER < 5.0% 8
5.0% <= ER < 10.0% 9
ER >= 10.0% 10
I created a UDF as:
Function Num2Cat(rCell As Range)
Dim v As Double, c As Integer
v = rCell.Value
On Error GoTo ErrorHandler
If Not IsNumeric(v) Then Exit Function
If v < -0.1 Then
c = 0
ElseIf v >= -0.1 And v < -0.05 Then
c = 1
ElseIf v >= -0.05 And v < -0.02 Then
c = 2
ElseIf v >= -0.02 And v < -0.005 Then
c = 3
ElseIf v >= -0.005 And v < -0.001 Then
c = 4
ElseIf v >= -0.001 And v < 0.001 Then
c = 5
ElseIf v >= 0.001 And v < 0.005 Then
c = 6
ElseIf v >= 0.005 And v < 0.02 Then
c = 7
ElseIf v >= 0.02 And v < 0.05 Then
c = 8
ElseIf v >= 0.05 And v < 0.1 Then
c = 9
Else
c = 10
End If
Num2Cat = c
ErrorHandler:
Exit Function
End Function
But is there a better way to define this UDF for my purpose? I feel the If, Elseif, Else statements are not that tidy at all.
Many thanks in advance.
Bookmarks