Spearman Rank Order Correlation Coefficient
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.
- Open the survey2000 data file.
- 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 childs 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 1st grade, Printing name by 1st
grade, Reciting alphabet by 1st grade, Counting to 100 by the 1st
grade) as a series of 1s and 2s. You will need to recode these data into
1s and 0s 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.
- 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.
- 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.
- 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.
- 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
- 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.
- 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:
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.
- 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:
Again, if you have added additional cases to your data file, this formula will be
slightly different, depending on the cell where you placed the Spearman rank order
How do you interpret these two values (Rho and Rho Squared)? What do they tell you
about these data?
Test of Statistical Significance for Spearmans Rho
Spearmans 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 Spearmans rho approximates the t
distribution. To perform this test, closely follow these instructions.
- 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.
- State the null hypothesis:
Ho: r s = 0.00
HA: r s /= 0.00
- 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.
- 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:
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 Spearmans 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 youve 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.
- Make a decision about the results.
In this case, do you reject or retain the null hypothesis? How do you evaluate your
results? Are the variables related in the population from which the sample was drawn? What
does all this mean?
Ó 2000, Lee Gustafson, Ph.D.