CODEWITHSUNDEEP
excelvbagroupingexcel-formula
BadgerBeaz
BadgerBeaz

Reputation: 393

Excel - Group Ages into predefined Categories

I've got these groupings with some population data I'm using in one column (Call it Data1):

10 to 14 years  
15 to 17 years  
18 and 19 years  
20 years  
21 years  
22 to 24 years  
25 to 29 years  
30 to 34 years  
35 to 39 years  
...  
85 years and over
Total

And I've got another piece of data (Call it Data2) I'm using which instead of grouping just has population by age from 1 to 100.

I need to group data2 into the same groupings as data1,

I started doing it with an Excel formula (using IF statements), but there are 24 different age groups so I quickly gave up with that.

is there an easier way to do it with formulas/vba/some other way?

Upvotes: 0

Views: 2402

Answers (3)

SeanC
SeanC

Reputation: 15923

if you are running excel 2007 or later, you have COUNTIF available.

10 to 14 years =COUNTIF(Data2,">=10",Data2,"<=14")
15 to 17 years=COUNTIF(Data2,">=15",Data2,"<=17")
18 and 19 years=COUNTIF(Data2,">=18",Data2,"<=19")
20 years=COUNTIF(Data2,"=20")
21 years=COUNTIF(Data2,"=21")

Etc...

Upvotes: 0

Brad
Brad

Reputation: 12253

If I understand your data it seems like Data2 is a raw list of ages and Data1 is a binned data.

You can move raw age data into bind by the array formula 

=FREQUENCY(<bins>,<Data2>)

where your bins will be 

10
15
18
20
21
22
25
30
35
40
45
50
55
60
65
70
75
80
85

additionally you could try this UDF if my first guess was not what you wanted...

Public Function Between(inputCell As Range, twoColBetweenRage As Range, outputLabel As Range)

    If twoColBetweenRage.Rows.Count <> outputLabel.Rows.Count Then
        Err.Raise 12345, "", "Input ranges are not equally sized"
        Exit Function
    End If

    Dim r As Integer, inputValue As Double
    inputValue = inputCell.Value
    For r = twoColBetweenRage.Row To twoColBetweenRage.Rows.Count
        If twoColBetweenRage(r, 1) <= inputValue And twoColBetweenRage(r, 2) >= inputValue Then
            Between = outputLabel(r, 1)
            Exit Function
        End If
    Next r

    If IsEmpty(Between) Then
        Err.Raise 12345, "", "No value was found"
        Exit Function
    End If

End Function

Upvotes: 1

barry houdini
barry houdini

Reputation: 46371

You can use a short formula if you construct a two column lookup table, e.g. in Y2 down put the lower bound of each range in ascending order, e.g. 10 in Y2, 15 in Y3, 18 in Y4 etc. then in the corresponding row in column Z you can put the group text, e.g. in Z2 "10 to 14 Years" in Z3, "15 to 17 Years" etc.

Now for a specific age in A1 you can get the correct group with this formula

=LOOKUP(A1,$Y$2:$Z$25)

Upvotes: 0

Related Questions