Formula to group into Age Ranges
 

In column K I have ages calculated from birthdates.

In Column L I would like to translate these ages into age groups, from which
I will later create a pivot chart to show the frequency of each age group.

I need a formula to translate the ages in Column K into age ranges in Column
L (16-20, 21-30, 31-40, 41-50, 51-60, 61-70, 71-80).

Can anyone tell me in layman's terms how this can be done and what I need to
enter where?

Thanks for reading!

Answer: Formula to group into Age Ranges
 

Sure, I can help you with that! Here's a

1. step-by-step guide

on how to create a formula to group ages into age ranges in Excel:

1. First, you need to decide on the age ranges you want to use. In your case, you've already listed them as 16-20, 21-30, 31-40, 41-50, 51-60, 61-70, and 71-80.

2. Next, you'll need to create a formula in Column L that will group the ages in Column K into the appropriate age range. Here's an example formula you can use:

This formula uses nested IF statements to check the age in Column K and assign it to the appropriate age range in Column L. You'll need to adjust the cell references (K2) to match the cell where your first age is listed.

3. Once you've entered the formula in the first cell of Column L, you can copy and paste it down the rest of the column to apply it to all the ages in your dataset.

4. Finally, you can use the age ranges in Column L to create a pivot chart that shows the frequency of each age group. To do this, select your data range (including both columns K and L), go to the Insert tab, and click on PivotChart. Follow the prompts to create your chart, and be sure to include the age range field in the chart's axis or legend.

Let me know if you have any questions or need further assistance.

Formula to group into Age Ranges
 

It is quite easy, i have done it in the past.

You need to do an IF statement on your ages,

Say you had

Age (column c)
16
21
30

and age groups of <16, 17 to 20 and 20 to 30.

=if(c1<16,"Less 16",if(c1<21,"17 to 20","20 to 30"))

Thats the general idea. I havent checked this, but thats the jist.


Sorry if its vague, just answering this whilst waiting for my own response :o)
"Donna" wrote:

In column K I have ages calculated from birthdates.

In Column L I would like to translate these ages into age groups, from which
I will later create a pivot chart to show the frequency of each age group.

I need a formula to translate the ages in Column K into age ranges in Column
L (16-20, 21-30, 31-40, 41-50, 51-60, 61-70, 71-80).

Can anyone tell me in layman's terms how this can be done and what I need to
enter where?

Thanks for reading!

Formula to group into Age Ranges
 

=HLOOKUP(K2,{16,21,31,41,51,61,71;"16-20","21-30","31-40","41-50","51-60","61-70","71-80"},2)
assuming your first age is in L2, copy formula to K2 and then copy down. If
your list starts further down, adjust as needed.
This formula will give you an N/A error if you have anyone under 16 and will
put everyone over 71 in the 71-80 age group. If there is any chance you
might have someone outside of the ranges you gave, this formula will add <16
for anyone under 16 and 80 for anyone over 80:
=HLOOKUP(K2,{0,16,21,31,41,51,61,71,81;"<16","16-20","21-30","31-40","41-50","51-60","61-70","71-80","80"},2)

"Donna" wrote:

In column K I have ages calculated from birthdates.

In Column L I would like to translate these ages into age groups, from which
I will later create a pivot chart to show the frequency of each age group.

I need a formula to translate the ages in Column K into age ranges in Column
L (16-20, 21-30, 31-40, 41-50, 51-60, 61-70, 71-80).

Can anyone tell me in layman's terms how this can be done and what I need to
enter where?

Thanks for reading!