When working with ages, it is common to group ages into categories, especially for the demographic data. Instead of reporting the statistics in each age, people normally report the results in age groups such as "less than 20 years old", "20 to 30 years old", "30 to 40 years old" etc.
If you have the date of birth, you will need to calculate the ages as of today or a specific date (e.g., September 30, 2015) using the formula below to get the age helper column (or check how to calculate ages in year):
=DATEDIF(B2,DATE(2015,9,30),"Y")
Where B2 is the cell with the date of birth.
1. If you have Excel 2019, you can use the IFS function with the formula below to group ages into different categories:
– less than 20 years old
– 20 to 30 years old
– 30 to 40 years old
– 40 to 50 years old
– more than 50 years old
=IFS(C2<20, "less than 20 years old", AND(C2>=20,C2<30),"20 to 30 years old",AND(C2>=30,C2<40),"30 to 40 years old",AND(C2>=40,C2<=50),"40 to 50 years old",C2>50,"more than 50 years old")
Where C2 is the age calculated based on the date of birth in B2, if you do not want to have a helper column, you can replace C2 with the formula =DATEDIF(B2,DATE(2015,9,30),"Y").
2. If you have the Excel 2016 and earlier, you can not use the IFS Function, but you can use the IF function with the formula below:
=IF(C2<20,"less than 20 years old", IF(AND(C2>=20,C2<30),"20 to 30 years old",IF(AND(C2>=30,C2<40),"30 to 40 years old",IF(AND(C2>=40,C2<=50),"40 to 50 years old",IF(C2>50,"more than 50 years old")))))
Where C2 is the age calculated based on the date of birth in B2.
The two formulas come with the same results, but you cannot use the IFS function in Excel 2016 and an earlier version.
If you want to group ages by month, please check how to calculate ages in month, then use the formula above to group ages into categories.
