Formula to group into Age Ranges

[Donna]

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!

 

[nathan_savidge]

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 )

 

[BoniM]

=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)