Sometimes we want to recode a variable into subgroups or categories. For example, we might want to create a variable with data on age recoded into age groups.

1. In the survey2000 data file, click on cell U1 and type Age 2 Groups. Press Enter.
2. We are now going to create a new variable, which will be Age, recoded into 2 groups. New coding categories for this variable will be 1 = persons whose age is less than 10 and 2 = persons whose age is 10 and over. We are going to use a logical IF statement to tell Excel how to do this.
3. In cell U2, type the formula =IF(J2<10,1,2) Column J contains the data for the variable Age, which we want to recode. We have now told Excel that if the value of cell J2 is less than 10, then put the number 1 in cell U2. Otherwise ("Else" in BASIC programming language), if the value in cell J2 is greater than or equal to 10, put the number 2 in cell U2. Press Enter. A number (1 or 2) should appear in cell U2.
4. Now we want to copy this formula into the other cells in column U. Click on cell U2. Move the cursor to the lower right hand corner of the cell until a black cross appears. Click and drag the cursor down column U to be bottom of the data file. Values for the recoded variable should appear in the cells. Click on a blank cell in the worksheet. You have now completed a simple, 2-group recode for the variable Age.

It is possible to recode variables into 3 or more groups in only two steps using a formula containing a series of nested IF function statements. The formula tells Excel to perform multiple logical functions in relation to recoding data. If you understand the logic of IF statements, you can recode your data into 3, 4, 5 or more groups using only one formula containing a series of nested IF statements.

1. Using the survey2000 data file, click in cell V1 and type Age 3 Groups. Press Enter.
2. We are now going to create a new variable in which Age will be recoded into 3 groups using a formula constructed using nested IF statements. New coding categories for this variable will be 1 = persons whose age is less than 10, 2 = persons whose age is 10-18, and 3 = persons whose age is 18 and over.

In cell V2 type the formula =IF(J2<10,1,IF(J2<18,2,3)) Press Enter.

A number (1,2, or 3) should appear in cell V2. The formula has given Excel this complex set of instructions: Look at cell J2 (the column where Age is located). If the value in cell J is less than 10, place a 1 in cell V2. If the value in cell J2 is greater than 10 but less than 18, place a 2 in cell V2. If the value in cell J2 is 18 or greater than 18, place a 3 in cell V2. Parentheses are used to nest the second IF statement within the first IF statement, so that Excel can read the formula and understand that it is supposed to carry out a series of operations based on a logical series of If-Then-Else statements.

3. Now we want to copy this formula into the other cells in column V. Click on cell V2. Move the cursor to the lower right hand corner of the cell until a black cross appears. Click and drag the cursor down column V to the bottom of the data file. Values for the recoded variable should appear in the cells. Click on a blank cell in the worksheet.

1. Using the survey2000 data file, click in cell W1 and type Age (IF) 4 Groups. Press Enter.
2. We are now going to create a new variable in which Age will be recoded into 4 groups using a formula constructed using nested IF statements. New coding categories for this variable will be 1 = persons whose age is less than 7, 2 = persons whose age is 7-8, 3 = persons whose age is 9-18, and 4 = persons age 18 and over.

In cell W2 type the formula =IF(J2<7,1,IF(J2<9,2,IF(J2<18,3,4)))

Press Enter.

A number (1,2,3, or 4) should appear in cell W2. The formula has given Excel this complex set of instructions: Look at cell J2 (the column where Age is located). If the value in cell J2 is less than 7, place a 1 in cell W2. If the value in cell J2 is greater than 7 but less than 9, place a 2 in cell W2. If the value in cell J2 is greater than 8 but less than 18, place a 3 in cell W2. If the value in cell J2 is 18 or greater than 18, place a 4 in cell W2. Parentheses are used to nest the second and third IF statements within the first IF statement, so that Excel can read the formula and understand that it is supposed to carry out a series of operations based on a logical series of If-Then-Else statements.

3. Now we want to copy this formula into the other cells in column W. Click on cell W2. Move the cursor to the lower right hand corner of the cell until a black cross appears. Click and drag the cursor down column W to the bottom of the data file. Values for the recoded variable should appear in the cells. Click on a blank cell in the worksheet.