Excel Tips – Calculating Age and Age Category
by Kelly Nortrom
Many people are intimidated by Microsoft Excel and miss out on some of the great tools Excel provides that make it easy to analyze and make sense of your data. Here are a few easy and intermediate tips using formulas to make your data more useful.
1. Age Calculation in Years
Let’s say that you have some basic demographic data for that includes birth date. You need to determine the age as of today and separate them into age classifications to coordinate with special population specialists (Kids = under age 18, Elders = age 60 and older). This will mean that we are calculating the difference between two dates with the result being a number.
I start with adding the headers on the new columns that I will create (“Age”, “Age Class”) and also add a cell with Today’s date so that we have a “point-in-time” where we are calculating the age.
To calculate the age, we click on cell E2 and enter this formula:
=(H1-D2)/365.25
We have to divide the difference between the two dates by 365.25 (the number of days in a year adjusted for leap years) to get the result to convert to the number of years. I then highlight column E, right click, and select Format Cells. I select Number and change the decimal places to 2. In order to make the formula above be able to copy down to the additional cells in column E, we need to make the reference to the date in cell H1 a constant (our point-in-time date). To do this, click back on cell E2, and in the formula field, manually change it to add a dollar sign ($) in two places like this:
=($H$1-D2)/365
You can now either click and drag that formula down column E or you can Fill Down.
Click and Drag method:
Click on cell E2, hover your mouse over the lower right corner until you get a cross symbol, click and drag your mouse down to the last entry in column E, then release.
 |
| Click and Drag Method |
|
Fill Down method:
Click on cell E2, hold the shift key down and then click on the last cell in column E to copy the formula to (in this case, E7). Select the Home tab, click on the fill icon 
in the Editing section, and select Down. This method is especially helpful when populating cells in very large Excel spreadsheets.
2. Age Category Using =IF statement
Now that we have determined our client’s ages, we need to set up a formula using IF statements to classify them into age groups. I find it easiest to start with either the youngest group or the eldest group so that the formula is less complex. To keep things simple, we are going to start by seeing what our formula would look like if we only classified our clients by either “Kid” versus “Adult”. IF statements in Excel have this syntax:
=IF([logical test],[what to do if logical test is true],[what to do if logical test is false])
So following that syntax, I enter this formula into cell F2:
=IF(E2<18,"Kid","Adult")
You would then copy this formula down column F by using either the click and drag method or the Fill Down method explained above.
3. Multiple Age Categories using Nested =IF statements
In our situation, we need to distinguish between three different scenarios (Kid, Adult, Elder) which will require us to do a more advanced formula called a nested IF statement. This means that the beginning portion of our IF statement can remain the same, but our “what to do if logical test is false” needs to be another IF statement to evaluate against the last two possible age categories.
There are several right ways to do our nested IF statement, but I’m going to just show one here. Please see the end of this blog entry for other examples of how to get to the same result. In cell F2, I enter this formula:
=IF(E2<18,"Kid",IF(E2>59.99,"Elder","Adult"))
You would then copy this formula down the column as you have done before. Essentially this IF statement broken down is saying,
If the age is less than 18, then enter “Kid”, otherwise if the age is over age 59.99, enter “Elder”, and everything else enter “Adult”.
Here is what your result should look like:
Other nested IF statements that would work for this situation:
=IF(E2>59.99,"Elder",IF(E2<18,"Kid","Adult"))
=IF(AND(E2<60,E2>17.99),"Adult",IF(E2>59.99,"Elder","Kid"))
=IF(AND(E2<60,E2>17.99),"Adult",IF(E2<18,"Kid","Elder"))
I hope that this is helpful to you and stay tuned for more blog entries with helpful hints!
