Excel - formula for calculating age bands, please!

[LeoZwalf]

Hi all,

I have a column containing people's ages. I need a column next to it showing their age band e.g. 16-24, 25-34 etc.

Anyone know how to use Excel formula to make this happen? I have tried using nested IF's but with no luck.

Thanks in advance,
Leo

[Plotloss]

=COUNTIF is your friend here.

[dougc]

My interpretation is thus:

In column A you have a list on numbers. You want to put a corresponding range in column B. If A1=27, you wish column B to return 25-34

I would probably go with a VLOOKUP using a hidden table with a list of ages, say 1 to 100 in column 1 and the correct banding for each age in column 2

Then in cell B1 type =VLOOKUP(A1,[your reference table],2,FALSE)

PM me and I'll send you an example if you like.

[LeoZwalf]

Hmm, you sure? Unless I'm misunderstanding - I don't need to count anything.

The data column is:

27
36
31
53
38
47
49
27
38
59
54
59


I need to add a column to the right of that which would look like this:

25-34
35-44
25-34
45-54
35-44
45-54
45-54
25-34
35-44
55-64
45-54
55-64


I've done it manually (sorting by age column and adding in the age bands by hand) so far but ongoing someone else will be doing it so I want it as automated as possible.

Wouldn't an IF statement (somehow) do the job?

I've bodged it for now by putting the ages and corresponding bands in another sheet and doing a VLOOKUP... not ideal but it seems to work.

[LeoZwalf]

dougc said:

My interpretation is thus:

In column A you have a list on numbers. You want to put a corresponding range in column B. If A1=27, you wish column B to return 25-34

I would probably go with a VLOOKUP using a hidden table with a list of ages, say 1 to 100 in column 1 and the correct banding for each age in column 2

Then in cell B1 type =VLOOKUP(A1,[your reference table],2,FALSE)

PM me and I'll send you an example if you like.

Just seen this after I posted my response! Thanks - we are on the same lines it seems

[dougc]

LeoZwalf said:

dougc said:

My interpretation is thus:

In column A you have a list on numbers. You want to put a corresponding range in column B. If A1=27, you wish column B to return 25-34

I would probably go with a VLOOKUP using a hidden table with a list of ages, say 1 to 100 in column 1 and the correct banding for each age in column 2

Then in cell B1 type =VLOOKUP(A1,[your reference table],2,FALSE)

PM me and I'll send you an example if you like.

Just seen this after I posted my response! Thanks - we are on the same lines it seems

No worries - only caveat is that two wrongs rarely make a right.

[2something]

Nested IFs should work, but they are always a bit tricky to get right. Something like this might work, I haven't checked it in Excel though.

IF(A1>34, IF (A1>44, If(A1>54, "55-64","45-54" ) ,"35-44" ), "25-34" )

Edited by 2something on Tuesday 8th April 11:30

Just checked it in Excel and

=IF(A1>34,IF(A1>44,IF(A1>54,"55-64","45-54" ),"35-44" ),"25-34" )

works very nicely, just adjust A1 to where your data starts.

Edited by 2something on Tuesday 8th April 13:08

[LeoSayer]

dougc said:

I would probably go with a VLOOKUP using a hidden table with a list of ages, say 1 to 100 in column 1 and the correct banding for each age in column 2

Then in cell B1 type =VLOOKUP(A1,[your reference table],2,FALSE)

[bam50]

2something said:

Nested IFs should work, but they are always a bit tricky to get right. Something like this might work, I haven't checked it in Excel though.

IF(A1>34, IF (A1>44, If(A1>54, "55-64","45-54" ) ,"35-44" ), "25-34" )

Edited by 2something on Tuesday 8th April 11:30

Leo try this:-

Assuming col A contains the ages this formula will determine if cell A5 is between 25-34:-
=IF(A5<25,0,IF(A5>34,0,1)) In English, if the value of cell A5 is less than 25 enter a 0, if the value of cell A5 is greater then 34 enter a 0, else enter a 1.

You'll then need to amend it for each column age range. E.g in the next column to determine if A5 is between 35-44:-
=IF(A5<35,0,IF(A5>44,0,1))

Once you've done all the age range column formulae you can simply select them all and drag them down the worksheet as per usual.

HTH

[CaptainSlow]

I'd go the vlookup route, however change the false to true and then only use the highest number in each limit in the rules table. This will save you listing all ages in the rules table.