Access Excel VBA group number with larger than smaller than (age group)

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

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

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *