# 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