Excel - formula for calculating age bands, please!
Discussion
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.
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.
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.
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.
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 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 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 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.
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" )
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.
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
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" )
Leo try this:-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
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
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff