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