Spearman rank order correlation coefficient (usually referred to as Spearman rho or Spearman r) is used to compute a measure of association between two variables when the variables are of ordinal levels of measurement.

1. Open the survey2000 data file.
2. Copy and paste the two variables you are going to analyze onto a new worksheet In this example, we will analyze the relationship between a child primarily being seen in an emergency room for medical problems (Column G, ER) and that child’s developmental level (Columns V – Z). Next, we will first need to create a new variable for developmental level. Notice that we originally coded developmental milestones (Columns V – Z; Walking by 18 months, Tying shoes by 1^st grade, Printing name by 1^st grade, Reciting alphabet by 1^st grade, Counting to 100 by the 1^st grade) as a series of 1’s and 2’s. You will need to recode these data into 1’s and 0’s by using a IF recode statement (=IF(V2<2,1,0)). Once all the Developmental variables are recoded in this fashion, we can develop a scale representing developmental progress by summing the milestones, with a 0 representing none of the milestones being met, a 1 representing limited achievement, a 2 representing slightly more development, and so forth up to a maximum of 5. This scale represents an ordinal ranking of how well each child was perceived to achieve this set of developmental milestones. Title this new variable you have created "Development." Copy and paste ER into column A with the label ER in cell A3 and the data for ER beginning in cell A4. Then copy and paste Development into column B of this new worksheet with the label Development in cell B3 and the data for Development beginning in cell B4.
3. In cell C3 type ER Ranked. In cell D3 type Development Ranked. In cell E3 type the letter D, and in cell F3 type D Squared.
4. Sort the data file by ER. In column C, the column labeled ER Ranked, you will assign a rank order, including ties, to the ER data. You must determine and input the rank order for each ER value. For tied values, assign the middle occurring rank for the set of tied values. For an odd number of ranks, assign the middle rank of the set of tied values. For an even number of ranks, assign the rank that is halfway between the two middle occurring ranks. For example, if the tied ranks were 5, 6, and7, you would assign a rank of 6 to all three of the tied ranks. The rank of the case following the three tied ranks would be 8. If the tied ranks were 5, 6, 7, and 8, you would assign a rank of 6.5 (halfway between the two middle ranks) to each of the four tied ranks. The next rank would be 9. Observe how the tied ranks are assigned in the example.
5. Then sort the data file by Development. In column D, the column labeled Development Ranked, assign a rank order, including ties, to the Development data using the process described in step 4.
6. In column E (which is the column you have labeled D) determine the difference for each set of ranks by entering the formula =(the cell address of the first case of the variable in column C) minus (the cell address of the first case for the variable in column D). In this example, click in cell E4 and type the formula =C4-D4. Press Enter. Then click in cell E4, position the cursor in the lower right hand corner of the cell so that it turns into a black cross, and fill in the formula by dragging the cursor to the end of the column.
7. In column F (labeled D Square) you will compute the square of the values in column E. Click in cell F4. Type the formula =(E4)^2. Press Enter. Then click in cell F4, position the cursor in the lower right hand corner of the cell so that it turns into a black cross, and fill in the formula by dragging the cursor to the end of the column.
8. Now compute the Spearman rank order correlation coefficient. The formula is



where the squared difference between a pair of ranks

the number of pairs.

In cell F53 (or another blank cell at the bottom of the data in column F) type Spearman r =. In cell H53 type this formula:

=1-(6*SUM(F4:F51))/(48*(48^2-1))

If you have added additional cases to your data file, this formula will be slightly different. See if you can figure out what the correct formula would be.

9. Beneath where you have placed the Spearman rho value, you will compute rho squared. In column F type Rho Squared =. In the adjacent H cell, type this formula:

=(H53)^2

Spearman’s rho (as all measures of association) can be tested for statistical significance. The null hypothesis is a statement that the population value (r _s) is actually 0 and, therefore, that the value of the sample Spearman rho is the result of mere random chance. When the number of cases in the sample is 10 or more, the sampling distribution of Spearman’s rho approximates the t distribution. To perform this test, closely follow these instructions.

1. Make your assumptions. In this case, our model is based on (1) random sampling, (2) ordinal level of measurement, and that (3) the sampling distribution is normal.
2. State the null hypothesis:

Ho: r _s = 0.00

H_A: r _s /= 0.00

3. Establish the critical region and sampling distribution:

Sampling distribution = t distribution

Alpha = .05

Degrees of freedom = N – 2

t (critical) = +/- 2.306

In cell F55, type Degrees of freedom and then press Enter. In the adjacent H cell (H55) type the value for the degrees of freedom for this test (46). In this example, there is an N of 48 (meaning a sample with 48 cases), so degrees of freedom = N-2, or 46.

4. Compute the test statistic:

The formula for computation of the Spearman rho test statistic:

T (obtained) =

 

To do this in Excel, follow these instructions:

In cell F56 (or beneath the cell where you placed the rho squared value) type t (obtained) and then press Enter. In cell H56, enter the following equation:

=(H53)*(SQRT((48-2)/(1-H54)))

This formula should return a t value [t (obtained)] for your Spearman rho statistic. If you place values in different areas of the worksheet, this formula will be slightly different, depending on the cells where you have placed the Spearman rank order correlation coefficient and r squared. The value of H53 should represent the location of the cell where you have placed Spearman’s rho and the value of H54 should represent the location of the cell where you have placed r squared. If you have added cases to your data set, the N value will also be different.

In cell F57, type p = and press Enter.

In the adjacent H column, insert a statistical function by clicking on the function icon beneath the menu bar at the top of you worksheet (¦ _x). Clicking on this icon will open a Paste function dialogue box. Select statistical from the "function category" and select TDIST from the "function name" side of the menu. Click on Okay.

A second dialogue box should open, requesting several values. With the cursor in the area titled "X," click on the cell containing your Spearman rho value (H53). Insert your cursor into the area titled "Deg_freedom" and then click in the cell where you’ve entered the value (H55). Finally, enter a 2 in the dialogue box area titled "Tails." Click on Okay.

A p value should appear in the cell. If it does, congratulations! This value is an evaluation of your Spearman rho statistic with respect to the t- distribution.

5. Make a decision about the results.

Ó 2000, Lee Gustafson, Ph.D.