Excel Snippets - Grouping people by age groups

Every once in a while, every excel user would need to group people in a list by age group. The prerequisite for this task is either an age column, or date of birth (DOB).

In case you have the DOB column. the first step is to add and populate the age column. the quick and easy solution is using the following formula:

 =INT(YEARFRAC(birthdate, TODAY()))

Basically, this formula fetches the integer part of year fraction of difference between the birthdate cell and today's date.

Now comes the actual grouping part. This approach assumes a fixed number of years groups and an upper max (optional). For example, we may need to group from 1 to 15, 16 to 30... and so on till 61 to 75, and finally 75+ (everyone older than 75 years). To achieve this, add another column to the table and name it "What was I thinking?" or anything you feel like. Then add the following formula assuming  H2 is the first record cell for Age that you got from the formula above:

=IF(H2>75,"75+",INT((H2-1)/15)*15+1&"-"&(INT((H2-1)/15)+1)*15)

Copy this formula to all the other cells in the Age group column using the auto-fill feature of excel, i.e. double clicking on the lower right corner of the original formula cell (where the cursor changes to a +). It should automatically convert the formula reference of H2 to the corresponding row numbers like H3, H4, H5.....

A little explanation of what happens in the formula, in case you are one of those who really need to know:

1. H2 is the cell address from where age value is picked up. 
2. H2>75,"75+" adds 75+ to the group column if the age is greater than 75
3. If H2 is not greater than 75 then the second part kicks in.
4. INT((H2-1)/15)*15+1 calculates the lower limit of the 15 years interval in which H2 falls.
5. &"-" adds a hyphen after the lower limit value.
6. (INT((H2-1)/15)+1)*15) calculates the upper limit of the 15 years interval in which H2 falls.

Remember, we decided in the beginning that we want to group into 15 years intervals with an upper limit of 75+. You may modify the formula to any other grouping simply by changing the 75 with whatever you want the upper limit to be, and replace all the 15s by the group intervals in years. For example, if you want to group into 5 years intervals with an upper limit of 55 years, your formula would be:

=IF(H2>55,"55+",INT((H2-1)/5)*5+1&"-"&(INT((H2-1)/5)+1)*5)

So there you go, age grouping conquered, next we move onto adding a wildcard search term in the IF statement to identify columns containing a certain text string anywhere within the cell contents.