Formula to group into Age Ranges

[Donna]

I have a spreadsheet that tallies age from dates of birth.

What I want to be able to do is group the ages in column K into age groups
in column L, i.e. 16-20, 21-30, 31-40, 41-50, 51-60, 61-70, 71-80.

I need very straightforward, step-by-step, no-jargon instructions as to how
to do this please!

Thank you...

 

[Pete_UK]

Set up a simple two-column table like this, perhaps starting in X1:

0 U16
16 16-20
21 21-30
31 31-40
41 41-50
51 51-60
61 61-70
71 71-80
81 80+

so that it occupies X1:Y9.

Then put this formula in L2:

=VLOOKUP(K2,X$1:Y$9,2)

assuming your first row of data is row 2. Then copy this formula down
for as many ages as you have.

Hope this helps.

Pete

 

[Donna]

Thank you - success at last!

Much appreciated, Pete.

 

[Pete_UK]

You're welcome, Donna - thanks for feeding back.

What you can also do now is to put this formula in Z1:

=COUNTIF(L:L,Y1)

and then copy this down to Z9. It will give you a count of each
category - no need for a pivot table.

Hope this helps.

Pete

 

[Donna]

Even better - brilliant.
Thanks.

You're welcome, Donna - thanks for feeding back.

What you can also do now is to put this formula in Z1:

=COUNTIF(L:L,Y1)

and then copy this down to Z9. It will give you a count of each
category - no need for a pivot table.

Hope this helps.

Pete

 

[Pete_UK]

Glad to be of help.

Pete