Printer Friendly

Using correlational and prediction data to enhance student achievement in K-12 schools: a practical application for school counselors.

Correlational data and regression analysis provide the school counselor with a method to describe growth in achievement test scores from elementary to high school. Using Microsoft Excel, this article shows the reader in a step-by-step manner how to describe this growth pattern and how to evaluate interventions that attempt to enhance achievement and to reduce the achievement gap among ethnic groups.

Juanita Smith, a sixth-grade counselor at Cesar Chavez Middle School in Hopeville, NC, has just returned from her first team meeting of the year with the four teachers who make up the sixth-grade Superstars team. As was the case last year, the teachers noted that their students didn't seem to be handling the transition from the smaller single-teacher, self-contained classes in elementary school to the multiple teachers, longer class periods, and changing classes that they were encountering in middle school. The students didn't seem to feel attached to the school and constantly complained that they wished they could go back to their old school. Academically, they seemed uninvolved and easily distracted, and the teachers thought that this response was particularly evident among the boys. The teachers worried that this lack of involvement would be reflected in disappointing scores on the standardized end-of-grade tests, just like last year. Because the goal of the school's student improvement plan is academic success for all students and closing the achievement gap between minority and White students, the teachers had asked Juanita whether the degree to which students felt attached to the school could impact their learning and ultimately predict their growth in achievement scores. If so, then they wanted to know what could be done right now to change how students felt about school.


In this article, we show how Juanita and other school counselors can use available data and both regression and correlational analyses to answer the questions that she has been asked. Our demonstration will employ Microsoft Excel, a common software program that is frequently bundled with both Windows- and Macintosh-based computers. The demonstration will proceed in a step-by-step manner so that it can be generalized to analyzing related data by counselors who may not feel especially confident in their data analysis skills.

By way of review, the reader may recall that the relationship between two variables such as achievement (end-of-grade scores, or EOG) and sense of belonging (feeling attached to school or a sense of community or relatedness in school) is frequently described by a correlation coefficient. Specifically, the correlation coefficient can vary between +1.00 and -1.00. A value of +1.00 means that there is a perfect positive linear relationship between the two variables so that the more attached that students feel to the school, the higher their EOG test scores will be. Conversely, a correlation of -1.00 would indicate a perfect negative linear relationship between belonging and EOG test scores such that a high attachment is associated with low test scores. Finally, a correlation of 0.00 means that there is no linear relationship between a student's feelings of attachment or belonging to school and EOG test scores.

If the two variables are positively correlated, we might speculate that a counseling or educational intervention that increases students' levels of attachment to school also might raise their test scores. However, we could not be certain of that as the correlation only tells us that the high scores on the two variables tend to go together and not necessarily that raising scores on one of them will cause an increase in scores on the other variable. If there is a causal relationship between the two variables, we do not know the direction of the effect. That is, attachment to school may be the cause of achievement, achievement may be the cause of attachment to school, or it is also possible that the two correlated variables have a common cause. Despite our lack of certainty, the speculation that increasing students' feelings of attachment might result in greater commitment to learning and therefore higher achievement is worth pursuing, and we show the reader how to do that later in the article.

In order to answer the teachers' question about the extent to which feeling attached to school is related to academic growth, we need to briefly review some information about regression analysis. In North Carolina as in other states, students are assessed in a number of academic domains at several points in time. Within a domain (e.g., math), we are interested in describing students' patterns of growth, both where they started and the rate of change. Once we have described the growth pattern for students, we then can look at the relationship between growth rate and sense of belonging. But belonging is not the only variable (correlate) about which the teachers were concerned. Teachers also expressed concern that gender and ethnicity might also be related to students' responses to the elementary-middle school transition. A few even confided to Juanita that parent involvement in the school might be an important factor in students' sense of belonging to the school. In addition, the students' test scores in math during elementary school were available to Juanita, who had every reason to believe that their past pattern of achievement on these tests might be described with regression analysis. Specifically, Juanita thought that, after plotting the achievement scores for a student as a function of "time," she could use regression analysis to describe the growth in achievement pattern in math for each student. In this article, we show the reader how that can be completed. We then show how to examine the relationships between rate of growth and other variables such as sense of belonging, gender, ethnicity, and parent involvement.

Given the academic record of a specific student, imagine plotting EOG scores as a function of time. Hopefully, we would observe a pattern of scores that starts in the lower left corner of the plot and rises to the right. If we were to construct a number of such plots for a number of students, we would note that different students exhibit different patterns, both in terms of "steepness" and "elevation." That is, different students "grow" at different rates, and they start in different places.

Regression involves fitting a line to existing data in order to describe a relationship. Going back to high school algebra, the reader may recall that the equation for a line is [gamma] = mx + b. In the simple regression case, we can think of x as being the time of the achievement test (in months), and [gamma] as the actual scores on our standardized (end-of-grade) achievement test measure. Now, m represents the slope of the line or the amount of change (or growth) in test scores ([gamma]) associated with one unit of change in the x scores (i.e., per month). The value of b represents the intercept or constant, that is, the value of the test score ([gamma]) when belonging (x) equals 0. Once we solve this equation and have obtained a slope and intercept for each student, we then can use the slopes and intercepts to look for relationships with other variables.

Let's return to how Juanita might have proceeded to address this situation. Juanita recalled that, in her graduate studies, she had encountered research indicating that the transition from elementary to middle school could have a negative impact on some students, a finding that the teachers on the Superstars team believed they had been witnessing. In reviewing some back issues of Professional School Counseling, she came across articles about the middle school transition (e.g., Akos, 2002; Akos & Galassi, 2004) in which this research was cited. Specifically, this transition has been associated with a variety of negative effects on adolescents, including declines in achievement (Alspaugh, 1998), decreased motivation (Anderman, Maehr, & Midgley, 1999), lowered self-esteem (Eccles et al., 1993; Wigfield, Eccles, Mac Iver, Reuman, & Midgley, 1991), and increased psychological distress (Chung, Elias, & Schneider, 1998; Crockett, Peterson, Graber, Schulenberg, & Ebata, 1989). In addition, boys tend to show a significant drop in academic achievement, while girls seem to experience a greater level of psychological distress after the transition (Chung et al.).

Moreover, a call to Juanita's former advisor in the school counseling program confirmed that satisfying students' need for belongingness has been shown to be associated with a host of desirable academic outcomes, including (a) a positive attitude toward school, class work, and teachers; (b) higher levels of achievement motivation and intrinsic academic motivation; (c) an increase in participation in school activities; (d) an increase in school engagement; (e) a decrease in likelihood for school dropout; and (f) a subsequent impact on achievement (Osterman, 2000). She also recalled from her multicultural counseling course that satisfying students' need for belongingness may be especially important for her Hispanic students, who composed a substantial percentage of the school's student body. Family and fitting into the group have been shown to be extremely important values for Mexicans and other Hispanic groups as a whole (Sue & Sue, 2003).

As a result, Juanita thought it was especially important in this instance to determine whether belonging was associated with growth in achievement. In order for Juanita to look at this relationship, she needs to use regression analysis to describe the growth pattern in achievement for each student. Let's look at how she might do that.


The measurement of change has a long history. Willett (1989), for example, summarized many of the issues well. A classic reference in the change literature is Harris (1963). Other references on the measurement of change that may be of some interest to the reader are Collins and Horn (1991) and Gottman (1995). With advances in both conceptualization and computer software, we have come a very long way from the days in which we simply looked at change as the difference between posttest and pretest. Currently, one of the more useful ways to study change is growth curve analysis, a relatively new technique to analyze data collected over time. More "technical" but fairly readable explanations of this technique can be found in Duncan, Duncan, Strycker, Li, and Alpert (1999), Little, Schnabel, and Baumert (2000), and Bollen and Curran (2006).

The basic idea is that we have a variable measured at several points in time. Here in North Carolina, elementary school students are tested on reading and mathematics at the beginning of third grade, at the end of third grade, and then at the end of each grade through Grade 8. The specific tests have been "calibrated" so that test scores are on a common scale across grade levels. Statisticians have developed sophisticated techniques for analyzing such data, but the basic idea is fairly simple. Although the procedure we describe in this article is not "precisely mathematically correct," it will allow school counselors to analyze their own data to look for possible patterns, which in turn might suggest areas in which to intervene. Furthermore, there is some research in the methodological literature to support our approach (Chou, Bentler, & Pentz, 2000).

Basically, we use "linear regression" to describe the growth pattern for each student. First, we can plot a student's test scores as a function of time. For example, suppose we have data on a particular student. (All data in this paper are simulated and thus fictitious; no substantive conclusions should be drawn from our "results.") For the four time points mentioned above, the scores on the student's mathematics achievement tests are 215, 226, 235, and 252 for the beginning of third grade and the end of third, fourth, and fifth grades, respectively. (For our illustration, we focus only on the mathematics achievement test scores, but Juanita would want to perform the same analysis with both the mathematics and the reading achievement test scores.)

Now, we need to "scale" time. The first time point is early September in third grade. We will set this time point equal to 0. The second time point is late May in third grade, or approximately 9 months after the first test. So, we will set this time point equal to 9. The last two tests are administered in late May of fourth grade and late May of fifth grade. Thus, they are 12 months and 24 months after the second time point, so we will set the times equal to 21 (9 + 12) and 33 (21 + 12). Now we can plot the data for this student in Microsoft Excel.

Entering the Data for a Student

The first step in this process is to use each student's achievement test scores to enter the data for a student so that we can find the regression line that describes the student's pattern of growth. There are several steps in the sequence:

1. Open up Microsoft Excel on your computer. You will see a blank worksheet. We are going to enter the data for this student in the first two columns.

2. In column A, we will enter the time variable. So we enter "0" in cell A1, "9" in cell A2, "21" in cell A3, and "33" in cell A4. Do not use the quotation marks; just enter the contents.

3. In a similar manner, we will enter the four test scores in column B. We enter "215" in cell B1, "226" in cell B2, "235" in cell B3, and "252" in cell B4. When entering the values, once again, do not include the quotation marks.

4. Go up to the toolbar at the top of the screen and click on "Insert" and then click on "Chart." A window will open, in which you specify the type of chart you want to create.

5. Click on "XY Scatter." In the next window that opens, the default is what you want, so go down to the bottom of the window and click on "Next." Yet another window will open.

6. In this window, you specify where the data are on the worksheet and how they are arranged. Given what we have entered, the data are in the first four rows of columns A and B. So, in the Data Range box, type "A1:B4"--again without the quotation marks. Then click on the button for "columns." Now, click on "Next" and you will get a preview of the chart. Click on "Next" and then click on "Finish." The plot of the data for this student will appear in your worksheet.

7. Move the mouse inside the chart, click on the left button, and drag the chart up to the top of the window, toward the right. Your worksheet should now look like Figure 1.


Finding the Regression Line for Each Student When we look at the four points plotted in the chart, it would appear that the "growth" in achievement for this student is roughly linear. That is, the rate of change appears to be roughly constant over time. The basic idea of linear regression is to "fit" a line through the points that estimates both where the student started (intercept) and the rate of change for this student (slope). Microsoft Excel also will allow us to obtain numerical estimates of these two values. This process also involves several steps:

1. With Microsoft Excel as the active window on your computer, click on the "Tools" option on the main toolbar. Down near the bottom, select "Data Analysis." (If you do not see the "Data Analysis" option, it may not have been installed. In that case, click on "Add-Ins" and when the next window opens, click on the box to the left of "Analysis Toolpak" and then click OK. After installation, you then should see "Data Analysis" when you click on "Tools.")

2. In the next window, scroll down and select "Regression."

3. In the next dialogue box, in the "Input Y range:" box, type "B1:B4" (once again, without the quotation marks). In the "Input X range:" box, type "A1:A4" (no quotes). In the "Output options" section, click on "Output Range:" and enter A10.

4. Then click OK. The results of the regression analysis will appear in the worksheet. Your results should look like Figure 2.


Of special interest are the values in cells B26 and B27. These are the estimates of the intercept and slope for this student. Looking at the overall pattern for this student, we estimate his or her initial score to be approximately 214.97 and the rate of growth to be 1.08. That is, for each month of time that passes, we estimate that this student's achievement score will increase by 1.08 points. Now, save your work under a file name that is meaningful to you, and remember to save often!

Building a Data File

At this point, we want to record the values for this student:

1. Put the cursor on cell B26 and click.

2. Now copy the contents of this cell (right mouse button [right arrow] copy).

3. Then click on the tab for Sheet 2 at the bottom of the screen, put the cursor in cell A3, and paste. You should see 214.97 appear. Note that we have labeled the column "Intercept" in cell A1.

4. Go back to Sheet 1, and copy the contents of cell B27 to cell B3 of Sheet 2. Note that we've labeled the column "Slope" in cell B1. We now have the intercept and the slope for the first student in our data file. We are going to build a data file that will contain the intercept and slope for each of our students.

Repeating the Process for the Rest of the Students

In similar fashion, we now can enter the data for another student:

1. Because the Microsoft Excel worksheet is "dynamic," all we have to do is to enter the four test scores for the next student. Suppose the scores are 221,230, 242, and 260, respectively.

2. Enter these four scores in cells B1 through B4. You will see the "plot" change as you enter the values.

3. Now repeat the process of estimating the regression for this student. Go back to the four steps in the section entitled "Finding the Regression Line for Each Student" to see how to do that. Let the program overwrite the previous results. Now copy the "intercept" (B26) and "slope" (B27) to Sheet 2 in cells A4 and B4, respectively.

4. Next, continue to do this for each of the students in your data set. We recognize that you may have approximately 100 students on a middle school team, but, for purposes of illustration, let's say that you have 30 students as the process is the same in either case. Also, you may not have complete data for every student. Although there are procedures for estimating values for missing data (e.g., Tabachnick & Fidell, 2001), they are beyond the scope of this article. We recommend that you eliminate the cases for which there are missing data as we are primarily interested in the overall pattern for the group at this point. When you have finished entering the data for each student, your data file in Sheet 2 should look like Figure 3.


Note that we have rounded the values to two decimal places. At this point, we have finished the hard part. Based on each student's test scores, we have estimated his or her starting point (intercept) and rate of change (slope).


Now the fun begins! Let's suppose that we have some additional information about the students. We know that when the children were in third grade, they responded to a questionnaire that assessed how much they felt themselves to be a part of the life of the school--a measure of school belongingness ("belong"). In addition, we know the gender of each child (1 = male, 2 = female), the ethnicity of each child (1 = minority, 2 = majority), and the number of PTA meetings during third grade at which they were represented by one or both parents (our measure of parent involvement). So, we enter all of that information into our Microsoft Excel worksheet, which will now look like Figure 4.


Finding Variable Correlations

Now that we finally have all of our data in one place, we can start to look for relationships. We will do this by looking at the intercorrelations among the variables. Once again, there are several steps to accomplish this:

1. Go back to the "Tools" option on the main toolbar, click it, and go down to "Data Analysis."

2. In the next window, select "Correlations."

3. In the next box, specify the input range to be "A3:F32" and that the data are grouped by columns.

4. Click the button to the left of "Output Range:" and specify "H5." When you click OK, a table of intercorrelations will be inserted into your worksheet. The worksheet should look like Figure 5.


Looking at the Relationships

Now we have some useful information. Recall that correlations range from 0 to 1 and can be either positive or negative. Values closer to 1 reflect stronger relationships. Positive values mean that higher scores on one variable are associated with higher scores on the other variable. Look at the correlation between column 1 and column 2; you should see that it is about .76. This means that there is a fairly strong relationship between the intercept and the slope, which means that students who started higher at the beginning of third grade tend to show more growth in achievement through the upper elementary grades. While that is an interesting finding that we will discuss shortly, of greater interest is the correlation between columns 2 and 3; r = .46. That suggests that pupils with a stronger sense of school belonging tended to show greater gains in achievement. Thus, if we could develop an intervention to increase the sense of school belonging, we just might be able to increase levels of achievement! No guarantee, but it's possible.

Also of passing interest are the correlations of gender (column 4) with the starting point (column 1) and rate of change (column 2). The correlation of approximately .28 between gender and starting point indicates a tendency for girls to be a little higher in achievement at the beginning of third grade. The correlation of .19 between gender and rate of change tells us that there is a mild tendency for girls to increase in achievement at a slightly higher rate. Of course, there are no interventions for gender, but this finding does give us some idea that we might try to design interventions specifically for boys.

Looking at the relationships of ethnicity to achievement, we see that there is a positive relationship between ethnicity and starting point (r = . 375) and between ethnicity and rate of change (r = .427). Furthermore, we can see that there is a relationship between ethnicity and sense of school belonging (r = .345). Putting this together, we might conclude that an intervention designed to increase sense of belonging targeted to children of color could be helpful in reducing the achievement gap.

When we look at the bottom row of correlations (for PTA attendance), we note that PTA attendance does not appear to correlate with any of our other variables. Thus, we might decide that encouraging more parents to attend more PTA meetings would not be helpful as far as increasing EOG scores is concerned.


Juanita presented the results of the correlational analysis to the Superstar team teachers. After discussing the findings, the teachers agreed with Juanita that it would be worthwhile to institute classroom-based interventions aimed at increasing students' feelings of belonging to the team and the school. The teachers and Juanita initially considered instituting a counselor-led classroom guidance unit directed at team building. However, this option was subsequently rejected because the team believed that it might result only in short-term benefits to the students and that classroom interventions occurring on a daily basis were needed in order to effect enduring changes in belonging.

After further consultation with her former advisor and some selected reading, Juanita recommended that the teachers incorporate two instructional interventions into their classes on a regular basis: cooperative learning and classwide peer tutoring.

Cooperative Learning

Cooperative learning methods involve students spending some of their class time working in heterogeneous (based on achievement) groups of four to six members with cooperative incentive structures in which they can earn recognition, rewards, or grades based on the academic performance of their group (Slavin, 1990). The group's success depends on the individual learning (individual accountability) of all members, and members help each other to prepare for quizzes or other forms of assessment that they take individually. Thus, students are responsible for their own learning as well as the learning of their teammates. The Superstars teachers occasionally had experimented with cooperative learning but felt that they had never stayed with it long enough to give it a true test.

In a review of the research on cooperative learning, Juanita found that students of all ability levels show higher academic achievement with cooperative learning and that females, minorities, and students at risk for school failure are especially likely to show increased achievement (Omrod, 1999). In addition, she noted that cooperative learning has produced positive changes in a variety of social and class climate variables, including peer support for academic achievement; more connectedness among students regardless of ability level, gender, or ethnicity; increases in cooperative and altruistic behavior; gains in student self-esteem; and increases in internalized motivation (Johnson & Johnson, 1994; Slavin, 1990). Based on Juanita's report of the research findings for cooperative learning, the Superstars teachers agreed to employ it on a regular basis in their curriculum.

Classwide Peer Tutoring

Classwide peer tutoring (CWPT), which was developed for instruction with children who are challenging to teach, was the second classroom intervention that the Superstars teachers agreed to implement (Greenwood, Carta, Kamps, & Hall, 1988). CWPT has been applied successfully to a variety of subjects with a range of students. It involves same-age, intraclass, peer-assisted instruction. Depending on the situation, students are either paired randomly or matched by ability for tutoring. All tutoring pairs are assigned to one of two teams that compete for the highest point total resulting from daily sessions in which individual students earn points for performance. Student roles (tutor-tutee) are switched (i.e., reciprocal tutoring) within daily tutoring sessions, and content, teams, and tutoring pairs are changed on a weekly basis. Tutoring sessions for the entire class occur at the same time. The teacher's role in CWPT is to organize tutoring content, prepare tutoring materials, train students in tutoring procedures, and monitor the process. Juanita reported that research has demonstrated that students are able to learn more in less time using CWPT when compared to conventional forms of teacher-directed instruction (Arreaga-Mayer, Terry, & Greenwood, 1998). The Superstars teachers agreed to implement CWPT for in-class homework assistance as a supplement to their traditional mathematics instruction procedures.


Any time that an intervention is implemented, it is imperative to assess whether it has had the intended impact. Given the manner in which we have shown how to describe the growth pattern of each student, assessing the impact is fairly straightforward. Recall that we scaled the "time of the test" in months. Thus, the slope (rate of change) for each student is the change in achievement score per month (see column B in Figure 3). For each student, we already have his or her EOG math score from the end of fifth grade. Assuming that we implement our intervention(s) at the beginning of sixth grade and that we will have the EOG math scores at the end of sixth grade, we need only to complete two calculations for each student.

First, we subtract the EOG fifth-grade math score from the EOG sixth-grade math score. Second, we divide that difference by 12, yielding the "points per month" growth during sixth grade. For example, look at row 25 in the spreadsheet in Figure 6. If that student had a score of 250 at the end of fifth grade and a score of 262 at the end of sixth grade, the student has gained 12 points; when the 12-point gain is divided by 12 months, we get a gain of 1.00 "points per month." In a similar fashion, using your achievement data (end of fifth grade and end of sixth grade), you can generate these "Gain 6" values for each student and enter the result for each student in column G of our Microsoft Excel worksheet. Note that we've labeled the column as "Gain 6." Your spreadsheet should now look like Figure 6.


Now, for each student we have two values, the points-per-month growth during elementary school (column B) and the points-per-month gained during sixth grade (column G). Given that we have two comparable values for each student, we would analyze our data with a dependent-samples t test to determine whether a significant change in achievement growth rate has occurred:

1. To do this in Microsoft Excel, click on "Tools" on the main toolbar. Then click on "Data Analysis," select "t test: Paired Two Sample for Means," and click OK.

2. When the dialogue box opens, specify the "Variable 1 range:" to be "G3:G32" (without the quotation marks). Specify the "Variable 2 range:" to be "B3:B32" (no quotes). We have entered the variables in this manner so that the way Excel reports the results will give us a positive difference if the growth rate in Grade 6 is bigger than it was during the elementary years.

3. In the "Hypothesized Mean Difference:" window, type "0."

4. Click the "Output Range:" button and specify the output range as "H14."

5. Click OK.

Your spreadsheet should now look like Figure 7.


In the table of results, you can see that the average gain per month in sixth grade is approximately 1.33 (variable 1) as opposed to the value of approximately 1.25 (variable 2) during the elementary years. Thus, the students have gained .08 points-per-month more in sixth grade than they did in the elementary grades. Because this increase in achievement growth rate might have happened by chance, we are going to test the likelihood that it might be due to chance with a paired-samples t test. The observed t statistic (t Stat) is about 3.07, with 29 degrees of freedom (df). Microsoft Excel automatically converts those two values to a probability; the one we want to use is labeled "P(T<=t) two-tail" and its value is .004586. That means that if the intervention makes no difference and .08 is a chance finding, we would expect to see a difference as big as the one we obtained (i.e., .08) less than 5 times in 1,000. Thus, it is not very likely that this is a chance finding, and it would appear that the intervention has had a positive effect.

Two issues need to be addressed related to this finding. First, can we attribute the positive result to our interventions? Literally, we cannot say that our intervention "caused" the increase in achievement growth rate as we did not use a control group design that would have allowed us to rule out other possible causes, such as possibly having better teachers in middle school "caused" it rather than our two interventions, CWPT and cooperative learning. However, given that the "base growth rate" was determined over three grades, we might argue that we have a stable estimate of growth rate over time. Therefore, it is not likely that the growth rate would have changed without the interventions.

The second point relates to the size of the difference. At first glance, a change in rate of eight-hundreds of a point per month may not sound very impressive. (Using statistical methods beyond the scope of this article, we can convert the change of .08 to an effect size of 0.56 standard deviations. In the social sciences and education, an effect size of .50 is regarded as a "medium-sized" effect.) Looking at the .08 difference, we note that the average "rate of gain" during the elementary years is 1.249. Thus, a difference of .08 is a gain of 6.4%, which appears to be a substantial contribution. Both of these authors would have been ecstatic had we received annual salary increases of 6.4% this past year.


A major concern in education is the well-documented tendency for minority students to perform at lower levels on standardized tests. This difference in apparent achievement is often called the "achievement gap." Given the way in which we have been analyzing our data up to this point, it is fairly easy for us to look at the possibility that our interventions may have had an effect on "closing the gap." That is, did the interventions have a greater effect for students of color (e.g., African American, Native American, and Hispanic students)?

To look at this possibility, we shall return to our most recent Excel spreadsheet (see Figure 7). We have the rate of growth (per month) during the elementary school years, and we have the rate of growth (per month) during sixth grade. These two variables are in column B and column G, respectively. In order for us to examine the question of "differential effects," we need to create a difference score:

1. The first thing we note is that the spreadsheet is getting a bit crowded. We need to create some room to calculate the difference scores. Put the cursor on the "H" at the top of the column and click the left mouse button. You should see all of column H highlighted.

2. Now go to the main toolbar and click on "Insert." Then, go down and click on "Columns." You should see the table of correlations and the results of the t test move to the right, leaving a blank column under the "H."

3. Now we are ready to calculate the difference score for each student. Put the cursor in cell H1 and type "Diff."

4. Put the cursor in cell H3 and type "=G3-B3"--once again without the quotation marks. You should see a value of "-0.1," which is the difference between 0.98 and 1.08.

5. Put the cursor in cell H3, hold the left mouse button down, and drag the cursor down the column to cell H32, inclusive. You should see the empty cells in column H highlighted.

6. Now type "Ctrl-D." You should see the cells fill up with values that are the differences between the values in column G and column B. We now have the differences in growth rates for each student.

Sorting Data for Minority, Majority Students

We now are ready to see if the differences for minority students are different from the differences for majority students. We will use an independent-samples t test. In order to do this, all the values for the minority students need to be adjacent, as do the values for the majority students. Thus, we need to sort the data:

1. Go back to the spreadsheet, put the cursor in cell A3, and hold the left mouse button down.

2. Drag the cursor across the row to cell H3. You should see the first row of data highlighted. Keep holding the left mouse button down.

3. Now drag the cursor from cell H3 to cell H32, and release the mouse button. You should see the entire block of data values highlighted.

4. Go up to the main toolbar and click on "Data." Then click on "Sort" and you should see a dialogue box open up. In the "Sort by" window, select "column E" as that is the column containing the values indicating ethnic group. Then click OK.

Your spreadsheet should look like Figure 8. Note that, in column E, all of the values of "1" are followed by all of the values of "2."


Assessing the Differences

We are now ready to assess whether the differences for the minority students are different from the differences for the majority students:

1. Go to the main toolbar and click on "Tools." Go down to the bottom of the menu and click on "Data Analysis."

2. In the dialogue box that opens up, scroll down and click on "t-test: Two-Sample Assuming Unequal Variances." Then click OK.

3. Another dialogue box will open. Next to "Variable 1 Range:" type "H3:H13," as the differences for the minority students (ethnic = 1) are in those rows of column H. In the box next to "Variable 2 Range:" type "H14:H32," as those cells contain the differences for majority students (ethnic = 2).

4. In the window next to "Hypothesized Mean Difference:" type "0." Now click the button next to "Output Range:" and enter "I34" in the window. Click OK.

At this point, your spreadsheet should look like Figure 9. You will need to scroll down the spreadsheet to see the results of the t test.


In looking at the results, first note the values in cells J37 and K37. It appears that the average difference in growth rates for the minority students is 0.14. The average difference in growth rates for the majority students is approximately 0.04. Thus, the difference between the "differences" is about 0.10, with minority students having a bigger difference than majority students. This finding would suggest that the interventions had a bigger, positive impact on the minority students. But, before we get too excited, look at the value in cell J45, .060931. There is the "probability" of seeing a difference between the two means as large as the one we obtained, assuming that there is no differential impact of the interventions on minority and majority students. In the social sciences, the conventional standard is that this value should be less than or equal to .05 in order to be "convincing." Thus, our data do not provide a strong argument that minority students are different from majority students regarding the impact of the interventions.

However, the results are promising. First, the results are in the right direction. Second, we are only dealing with a total of 30 students. With more students in the file (i.e., data for all of the students on the Superstars team), the difference of 0.10 probably would have yielded a probability less than .05. As a result of this analysis, we are left thinking that the interventions may hold promise for closing the achievement gap.


Recent reform efforts in school counseling such as the Transforming School Counseling Initiative (Education Trust, i999) and the ASCA National Model[R] (American School Counselor Association, 2005) have emphasized the importance of school counselors functioning as educational leaders in the 21st century. The primary role of these leaders is to enhance academic success for all students and to narrow the achievement gap between students of color and European American (White) students and some Asian American student groups. In this role, school counselors need to be results-oriented in their efforts. Specifically, they must demonstrate a variety of skills and knowledge, including how to analyze and disaggregate local achievement data, where to find information about effective academic interventions, and how to determine the effectiveness of interventions applied in their schools to enhance academic success for all students and to narrow the achievement gap. In this article, using a middle school example, we have attempted to demonstrate one step-by-step approach to how school counselors can employ correlational data and regression analysis to meet this challenge.


Akos, P. (2002). Student perceptions of the transition from elementary to middle school. Professional School Counseling, 5, 339-345.

Akos, R, & Galassi, J. P. (2004). Middle and high school transitions as viewed by students, parents, and teachers. Professional School Counseling, 7, 212-221. Alspaugh, J.W. (1998). Achievement loss associated with the transition to middle school and high school. Journal of Educational Research, 92, 20-25.

American School Counselor Association. (2005). The ASCA national model: A framework for school counseling programs (2nd ed.). Alexandria, VA: Author.

Anderman, E.M., Maehr, M. L, & Midgley, C. (1999). Declining motivation after the transition to middle school: Schools can make a difference. Journal of Research and Development in Education, 32, 131 - 147.

Arreaga-Mayer, C., Terry, B. J., & Greenwood, C. R. (1998). Classwide peer tutoring. In K. Topping & S. Ehly (Eds.), Peer-assisted learning (pp. 105-119). Mahwah, N J: Erlbaum.

Bollen, K. A., & Curran, R J. (2006). Latent curve models: A structural equation approach. New York: Wiley.

Chou, C., Bentler, P. M., & Pentz, M. A. (2000). A two-stage approach to multilevel structural equation models: Application to longitudinal data. In T. D. Duncan, K. U. Schnabel, & J. Baumert (Eds.), Modeling longitudinal and multilevel data: Practical issues, applied approaches, and specific examples (pp. 33-49). Mahwah, N J: Erlbaum.

Chung, H., Elias, M., & Schneider, K. (1998). Patterns of individual adjustment changes during middle school transition. Journal of School Psychology, 36, 83-101.

Collins, L. M., & Horn, J. L. (Eds.). (1991). Best methods for the analysis of change: Recent advances, unanswered questions, and future directions. Washington, DC: American Psychological Association.

Crockett, L., Peterson, A., Graber, J., Schulenberg, J., & Ebata, A. (1989). School transitions and adjustment during early adolescence. Journal of Early Adolescence, 9, 181-210.

Duncan, T. E., Duncan, S. C., Strycker, L. A., Li, F., & Alpert, A. (1999). An introduction to latent variable growth curve modeling: Concepts, issues, and applications. Mahwah, N J: Erlbaum.

Eccles, J. S., Wigfield, A., Midgley, C., Reuman, D., Mac Iver, D., & Feldlaufer, J. (1993). Negative effects of traditional middle schools on students' motivation. Elementary School Journal, 93, 553-574.

Education Trust. (1999). Transforming school counseling. Retrieved January 18, 2002, from

Gottman, J. M. (Ed.). (1995). The analysis of change. Mahwah, N J: Erlbaum.

Greenwood, C. R., Carta, J. J., Kamps, D., & Hall, E. V. (1988).The use of classwide peer tutoring strategies in classroom management and instruction. School Psychology Review, 17, 258-275.

Harris, C. W. (1963). Problems in the measurement of change. Madison, WI: University of Wisconsin Press.

Johnson, R.T., & Johnson, D.W. (1994). An overview of cooperative learning. In J. S. Thousand, R. A Villa, & A. I. Nevin (Eds.), Creativity and collaborative learning: A practical guide to empowering students and teachers (pp. 31-58). Baltimore: Paul H. Brookes.

Little, T. D., Schnabel, K. U., & Baumert, J. (2000). Modeling longitudinal and multilevel data: Practical issues, applied approaches, and specific examples. Mahwah, N J: Erlbaum.

Omrod, J. E. (1999). Human learning (3rd ed.). Upper Saddle River, N J: Prentice-Hall.

Osterman, K. F. (2000). Students' need for belonging in the school community. Review of Educational Research, 70, 323-367.

Slavin, R. E. (1990). Cooperative learning: Theory, research, and practice. Upper Saddle River, N J: Prentice Hall.

Sue, D.W., & Sue, D. (2003). Counseling the culturally diverse: Theory and practice (4th ed.). New York: John Wiley.

Tabachnick, B. G., & Fidell, L. S. (2001). Using multivariate statistics (4th ed.). Boston: Allyn and Bacon.

Wigfield, A., Eccles, J. S., Mac Iver, D., Reuman, D., & Midgley, C. (1991).Transitions during early adolescence: Changes in children's domain-specific self-perceptions and general self-esteem across the transition to junior high school. Developmental Psychology, 27, 552-566.

Willett, J. B. (1989).Questions and answers in the measurement of change. In E. Z. Rothkopf (Ed.), Review of research in education (pp. 345-422). Washington, DC: American Educational Research Association.

William B. Ware is a professor in the Educational Psychology program, and John P. Galassi is a profesor in the School Counseling program, in the School of Education, University of North Carolina at Chapel Hill. E-mail:

The authors gratefully acknowledge the assistance of Kate Lepley and Barbara M. Miller in the preparation of this manuscript.
COPYRIGHT 2006 American School Counselor Association
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2006, Gale Group. All rights reserved. Gale Group is a Thomson Corporation Company.

Article Details
Printer friendly Cite/link Email Feedback
Author:Galassi, John P.
Publication:Professional School Counseling
Geographic Code:1USA
Date:Jun 1, 2006
Previous Article:Does psychotherapy help some students? An overview of psychotherapy outcome research.
Next Article:Using comparison groups in school counseling research: a primer.

Related Articles
Transforming school counseling practice through collaboration and the use of data: a study of academic failure high school.
Advocacy as a critical role for urban school counselors: working toward equity and social justice.
Group counseling: beyond the traditional.
Linking school counselors and student success: a replication of the student success skills approach targeting the academic and social competence of...
The ASCA National Model, accountability, and establishing causal links between school counselors' activities and student outcomes: a reply to sink.
Evaluation of an innovative approach to improving middle school students' academic achievement.
Using comparison groups in school counseling research: a primer.
Practical significance: the use of effect sizes in school counseling research.
Resources for school counselors and counselor educators: The Center for School Counseling Outcome Research.
Research methods in school counseling: a summary for the practitioner.

Terms of use | Copyright © 2017 Farlex, Inc. | Feedback | For webmasters