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)

sInput	User input
interval	Interval within each group. For example, the interval is 4 for  grouping>18 to <=22
stGrp	Starting group number
edGrp	Ending group number

Example of custom Function wGroupDecimal

Formula	Result
=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)

sInput	User input
interval	Interval within each group. For example, interval is 5 for grouping1 to 5, 6 to 10
stGrp	Starting group number
edGrp	Ending group number

Example of custom Function wGroupInt

Formula	Result
=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