Any ideas how to determine what age group an indiv

问题:

I have 2 columns in an Excel spread sheet.

First column header is called AGE and the second column header is called Age_Group.

The ages in the AGE column are between 18 and 45.

There could be multiple rows with same age.

I am tasked with determining what age group a certain age falls into and then print out that age group.

For instance, if someone’s age is 18, I would like to use IF statement or CASE statement to determine that age 18 is between 18 and 24 and then put that value into Age_Group column as 18-24.

If age is 19, again, it will fall into the group of 18-24 in Age_Group column.

If the age in the Age column falls between 25 and 29, then they will be in Age_Group column as 25-29. Here is an example:

AGE                            AGE_GROUP
18                               18-24
19                               18-24, 
25                               25-29
28                               25-29

If you would be kind to share some code, I can use it to calculate the rest till we reach 45.

I am not good with Excel VBA. Any assistance with IF or CASE statement would be greatly appreciated.

回答1:

Here is a VBA code: But surely you are better off with that Excel VLOOKUP. :)

1. First add module to a VBA project.

2. Copy the following

code.

Option Explicit


Function whatAgeGroup(ByVal i As Integer) As String
    Select Case i
        Case IsEmpty(i)
            whatAgeGroup = "No Age Entered"
        Case 18 To 24:
            whatAgeGroup = "18-24"
        Case 25 To 29
            whatAgeGroup = "25-29"
        Case 30 To 34
            whatAgeGroup = "30-34"
        Case 35 To 39
            whatAgeGroup = "35-39"
        Case 40 To 44
            whatAgeGroup = "40-44"
        Case Is >= 45
            whatAgeGroup = "45 and Above"
        Case Else
           whatAgeGroup = "WhatGroup?"
        End Select
End Function

1. enter the function name in a cell and enter, as per the image shown.

回答2:

Use a vlookup. Create a two column table with the lower bound of your age range in the first column and the age range as text in the second column like so:

AGE_LOOKUP  AGE_GROUP
18          18-24
25          25-29
30          30-35
36          36-37 etc..

Name this table's range as AGE_GROUP_TABLE then using the following formula to get the age group:

VLOOKUP(A1, AGE_GROUP_TABLE, 2, TRUE)

Put different ages into cell A1 and the formula will return the correct age group. The important thing to remember is to make sure that the last parameter of the VLOOKUP is set to TRUE, this makes the VLOOKUP find the nearest match.

回答3:

As the idea of a Table was causing some confusion I thought I would also share my favourite trick of a self contained, table free, VLOOKUP using an embedded array. So for the current problem it would look something like this:

VLOOKUP(A1, {18,"18-24";25,"25-29";30,"30-35";36,"36-37"}, 2 TRUE)

回答4:

While the vlookup solution is excellent, you can also do that with a PivotTable. Make sure the age is the row heading, then right click the Age "square" and select Group. This is avery easy and dynamic.