Grouping Data in Microsoft Excel

T

taytoman

Registered User
Messages
251
I know this is probably not a post for askaboutmoney, but its worth a try anyway!

I have done a research project, and need to recategorise data within an excel spreadsheet. I have 1000 people aged between 20-60, and want to recode them into a new variable called agegroup, which will have 4 categories: 20-30, 30-40,40-50, 50-60.

Any idea how this can be done in excel?
 
If you have a sheet with the ranges in Col A. (A1=0, A2=10, A3=20 etc.) and Col B the labels (B1 = "group 1" etc.) then the formula
=VLOOKUP(D1,A$1:B$5,2)
(I've shown for 5 groups)
Will return the value from the label colum (in this case depending on what's in D1)
"Group 1" if 0<=d1<10
"Group 2" if 10<=d1<20
and so on.
 
First thing if you use "20-30" and "30-40" as criterea you will get everyone age 30 showing in both categories!
You need to adjust it so it is something like ">=20 and< 30" etc...

If you have their ages listed in column C starting at cell C2
then start a new column(D) for "AGEGROUP" and enter in D2:

=IF(C2<20,"ERROR",IF(AND(C2>=20,C2<30),"Group1",IF(AND(C2>=30,C2<40),"Group2",IF(AND(C2>=40,C2<50),"Group3",IF(AND(C2>=50,C2<60),"Group4","Over60")))))
[there are NO spaces in the formula! But I can't stop them appearing on AAM for some reason!!!]
Anyone less than 20 will show up with ERROR
Anyone >=20 and <30 will show up with Group1
Anyone >=30 and <40 will show up with Group2
Anyone >=40 and <50 will show up with Group3
Anyone >=50 and <60 will show up with Group4
Anyone <60 will show up with Over60

You can copy or autofill the formula down column D. You can then sort and/or autofilter and/or group by column D
HTH
 
Thank you very much for your help
This saves me loads of hassle and avoids have to use SPSS!!
 
You must log in or register to reply here.
Back
Top