2015年3月25日 星期三

Access Excel VBA group number with larger than smaller than

This Access Excel tutorial explains how to create custom Function to categorize /group number with larger than smaller than symbol.


Access Excel categorize / group number with larger than smaller than symbol


Sometimes when you have a group of numbers, you may need to categorize / group them into different categories for analysis.


For example, you may need to make age group for employees, such as


<20


>=20 to <30


>=30 to <40


>=40


In order to handle decimal places, you must use the equal sign in >=, otherwise it would be confusing whether 20.5 is larger than 20.


To do this, I have written several custom Excel Functions to perform this task.


Custom Function 1A – Categorize / group decimal number (> and  <=)


The below Function is for grouping decimal number with > and <=


For example


<=20


>20 to <=30


>30 to <=40


>40


You can also use this Function to group integer, but I prefer to use grouping of “x to y” instead.


VBA Code of custom Function wGroupDecimal


Public Function wGroupDecimal(sInput, interval, stGrp, edGrp) As String
    If sInput <= stGrp Then
        wGroupDecimal = "<=" & stGrp
    ElseIf sInput > edGrp Then
        wGroupDecimal = ">" & edGrp
    ElseIf sInput > stGrp And sInput <= edGrp Then
        k = 0
        Do While stGrp + k < sInput
            If stGrp + k + interval > edGrp Then
                wGroupDecimal = ">" & (stGrp + k) & " to <=" & edGrp
            ElseIf stGrp + k + interval <= edGrp Then
                wGroupDecimal = ">" & (stGrp + k) & " to <=" & (stGrp + k + interval)
            End If
            k = k + interval
        Loop
    Else: wGroupDecimal = "failed to group"
    End If
End Function

Syntax of custom Function wGroupDecimal


wGroupDecimal(sInput, interval, stGrp, edGrp)









sInputUser input
intervalInterval within each group. For example, the interval is 4 for  grouping>18 to <=22
stGrpStarting group number
edGrpEnding group number

Example of custom Function wGroupDecimal












FormulaResult
=wGroupDecimal(17,4,18,40)<=18
=wGroupDecimal(18,4,18,40)<=18
=wGroupDecimal(18.1,4,18,40)>18 to <=22
=wGroupDecimal(60,4,18,40)>40

Custom Function 1B – Categorize / Group decimal number (>= and  <)


The below Function is for grouping decimal number with >= and <


For example


<20


>=20 to <30


>=30 to <40


>=40


You can also use this Function to group integer, but I prefer to use grouping of “x to y” instead.


VBA Code of custom Function wGroupDecimal


Public Function wGroupDecimal(sInput, interval, stGrp, edGrp) As String
    If sInput < stGrp Then
        wGroupDecimal = "<" & stGrp
    ElseIf sInput >= edGrp Then
        wGroupDecimal = ">=" & edGrp
    ElseIf sInput >= stGrp And sInput < edGrp Then
        k = 0
        Do While stGrp + k <= sInput
            If stGrp + k + interval >= edGrp Then
                wGroupDecimal = ">=" & (stGrp + k) & " to <" & edGrp
            ElseIf stGrp + k + interval < edGrp Then
                wGroupDecimal = ">=" & (stGrp + k) & " to <" & (stGrp + k + interval)
            End If
            k = k + interval
        Loop
    Else: wGroupDecimal = "failed to group"
    End If
End Function

Custom Function 2 – Categorize / Group integer number


The below Function is design for grouping integer using  “x to y” except that it uses < for start group and > for end group.


For example


<21


21 to 30


31 to 40


>40


VBA Code of custom Function wGroupInt


Public Function wGroupInt(sInput, interval, stGrp, edGrp) As String
Dim lowerBound, upperBound As Integer
    If sInput < stGrp Then
        wGroupInt = "<" & stGrp
    ElseIf Int(sInput) > Int(edGrp) Then
        wGroupInt = ">" & edGrp
    ElseIf sInput >= stGrp And Int(sInput) <= Int(edGrp) Then
        k = 0
        lowerBound = stGrp
        upperBound = lowerBound + interval
        Do While upperBound < edGrp + interval
            If sInput >= lowerBound And sInput < upperBound And upperBound - 1 <= edGrp Then
                wGroupInt = lowerBound & " to " & upperBound - 1
                Exit Function
            ElseIf sInput >= lowerBound And sInput < upperBound And upperBound - 1 >= edGrp Then
                    wGroupInt = lowerBound & " to " & edGrp
                    Exit Function
            Else
                k = k + interval
                lowerBound = stGrp + k
                upperBound = lowerBound + interval
                wGroupInt = lowerBound & " to " & edGrp
            End If
        Loop
    Else: wGroupInt = "failed to group"
    End If
End Function

Syntax of custom Function wGroupInt


wGroupInt(sInput, interval, stGrp, edGrp)









sInputUser input
intervalInterval within each group. For example, interval is 5 for grouping1 to 5, 6 to 10
stGrpStarting group number
edGrpEnding group number

Example of custom Function wGroupInt












FormulaResult
=wGroupInt(17,4,18,40)<18
=wGroupInt(18,4,18,40)18 to 21
=wGroupInt(18.1,4,18,40)18 to 21
=wGroupInt(60,4,18,40)>=41

 Outbound References


http://www.mrexcel.com/forum/excel-questions/401836-categorize-set-number-into-different-group.html



Access Excel VBA group number with larger than smaller than

沒有留言:

張貼留言