Formula to group into Age Ranges
 

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...

Formula to group into Age Ranges
 

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

On Apr 22, 11:01*am, Donna wrote:
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...

Formula to group into Age Ranges
 

Thank you - success at last!

Much appreciated, Pete.

"Pete_UK" wrote:

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

On Apr 22, 11:01 am, Donna wrote:
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...

Formula to group into Age Ranges
 

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

On Apr 22, 12:22*pm, Donna wrote:
Thank you - success at last!

Much appreciated, Pete.



"Pete_UK" wrote:
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

On Apr 22, 11:01 am, Donna wrote:
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...- Hide quoted text -

- Show quoted text -

Formula to group into Age Ranges
 

Even better - brilliant.
Thanks.

"Pete_UK" wrote:

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

On Apr 22, 12:22 pm, Donna wrote:
Thank you - success at last!

Much appreciated, Pete.



"Pete_UK" wrote:
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

On Apr 22, 11:01 am, Donna wrote:
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...- Hide quoted text -

- Show quoted text -

Formula to group into Age Ranges
 

Glad to be of help.

Pete

On Apr 22, 1:53*pm, Donna wrote:
Even better - brilliant.
Thanks.

Whoa! Great HELP!