This is one of the following four articles on Correlations in Excel

Overview of Correlation In Excel 2010 and Excel 2013

Pearson Correlation in 3 Steps in Excel 2010 and Excel 2013

Pearson Correlation – Calculating r Critical and p Value of r in Excel

Spearman Correlation in 6 Steps in Excel 2010 and Excel 2013

# Spearman Correlation in 6

Steps in Excel

The Spearman Correlation Coefficient is designated by either r_{s} or by the Greek letter ρ, “rho.” As mentioned __the Spearman correlation should be used instead of the Pearson correlation in any of the following circumstances: __

1) An X-Y scatterplot of the data indicates that there is a nonlinear monotonic relationship between two variables. Monotonic simply means that one variable generally goes in one direction (either always up or always down) when the other variable moves in one direction.

2) There are significant outliers. The Pearson Correlation is very sensitive to outliers. The Spearman Correlation is not because the Spearman Correlation bases its calculation on the ranks and not the mean (as the Pearson Correlation does).

3) At least one of the variables is ordinal. An ordinal variable is one in which the order matters but the difference between values does not have meaning. A customer satisfaction scale or a Likert scale are examples of ordinal data. Satisfaction scales and Likert scales can be analyzed as interval data if the distance between values is considered to be the same. A Pearson correlation can be used if the variables are either interval or ration but cannot be used if any of the variables are ordinal.

## Spearman Correlation Formula

The Spearman Correlation Coefficient is defined as the Pearson Correlation Coefficient between ranked variables. The Spearman Correlation is sometimes called the Spearman Rank-Order Correlation or simply Spearman’s rho (ρ) and is calculated as follows:

*(Click On Image To See Larger Version)*

For a sample of n (X-Y) data pairs, each X_{i},Y_{i} are converted to ranks x_{i},y_{i} that appear in the preceding formula for Spearman’s rho.

### Tied Data Values

Tied valued of X or Y are assigned the average rank of the tied values. For example, if the 2rd, 3^{rd}, and 4^{th} X value were equal to 19, the rank assigned to each would be 3. This is the average rank, which would be calculated as follows:

Average rank = (Sum of ranks)/(Number of ranks) = (2 + 3 + 4)/3 = 3

The RANK.AVG() formula will correctly calculate ranks if there are tied values among the values to be ranked.

### No Ties Among Data Values

If there are no tied values of X or Y, the following simpler formula can be used to calculate Spearman’s rho:

*(Click On Image To See Larger Version)*

## Spearman Correlation’s Only

Two Required Assumptions

1) The variables can be ratio, interval, or ordinal, but not nominal. Nominal variables are simply labels whose order doesn’t mean anything. The Spearman Correlation is nonparametric, i.e., the test’s outcome is not affected by the distributions of the data being compared.

2) There is a monotonic relationship between the two variables.

## Example of Spearman

Correlation in Excel

The Spearman Correlation Coefficient will be calculated for the following data:

### Step 1 – Plot the Data to Check For a Monotonic Relationship

*(Click On Image To See Larger Version)*

A monotonic relationship exists is one variable generally moves in a single direction (either increasing or decreasing) as the other variable moves in a single direction. A monotonic relationship does not imply linearity. A monotonic relationship appears to exist between the X and Y variables. X values generally increase as Y values increase.

### Step 2 – Automatically Checking For Tied X or Y Values in a Data Column

Checking a column of data for tied values can be automated in Excel. The cell U8 has the following formula:

=IF(SUM(IF(FREQUENCY($S$9:$S$15,$S$9:$S$15)>0,1))=COUNT($S$9:$S$15),“There Are No Tied Values”,”There Are Tied Values”)

In this case, there were no tied values from S9 to S15, as is demonstrated by the output of the above formula in cell U8 which states “There Are No Tied Values.”

Cell U18 contains a similar formula.

*(Click On Image To See Larger Version)*

**No Tied Values**

### Step 3 – Calculate the Ranks of the X and Y Values

This can be done in a single step in Excel with the RANK.AVG() formula as follows:

*(Click On Image To See Larger Version)*

The formula RANK.AVG() would also correctly calculate the ranks if there were any tied values.

### Step 4 – Calculate the Sum of the Square of the Rank Differences

*(Click On Image To See Larger Version)*

### Step 5 – Calculate r_{s}

*(Click On Image To See Larger Version)*

*(Click On Image To See Larger Version)*

r_{s} is calculated in cell BH13 by the following formula:

=1-((6*BH8)/((BH10*(BH10^2-1))))

r_{s} = 0.6786

### Step 6 – Determine If r_{s} Is Significant

__Method 1 – Compare r _{s} to r Critical__

*(Click On Image To See Larger Version)*

*(Click On Image To See Larger Version)*

r Critical in cell BS18 is calculated by the following formula:

=T.INV(1-AG16/2,AG14)/SQRT(T.INV(1-AG16/2,AG14)^2+AG14)

r Critical = 0.7545

r_{s} is not significant at α = 0.05 because r_{s} (0.6786) is less than r Critical (0.7545).

__Method 2 – Compare the p Value to Alpha__

*(Click On Image To See Larger Version)*

*(Click On Image To See Larger Version)*

The p Value in cell BS29 is calculated by the following formula:

=1-F.DIST(((AG14*S15^2)/(1-S22^2)),1,AG14,TRUE) = 0.0536

r_{s} is not significant at α = 0.05 because the p Value (0.0536) is greater than Alpha (0.05). This r_{s} would be significant at α = 0.10 but not at α = 0.05.

## If There Are Any Tied X or Y Values

### Step 3 – Calculate r_{s}

*(Click On Image To See Larger Version)*

*(Click On Image To See Larger Version)*

Cell AN58 contains the formula: =SUM(AN50:AN56) = 68.96

Cell AP58 contains the formula: =SUM(AP50:AP56) = 105.93

Cell AQ58 contains the formula: =SUM(AQ50:AQ58) = 83.36

Cell AP61 contains the formula: =SQRT(AP58*AQ58) = 93.97

*(Click On Image To See Larger Version)*

r_{s} is calculated in cell AP66 by the formula: =AN58/AP61 = 0.7339

### Step 4 – Determine If r_{s} Is Significant

__Method 1 – Compare r _{s} to r Critical__

*(Click On Image To See Larger Version)*

*(Click On Image To See Larger Version)*

r Critical in cell BS18 is calculated by the following formula:

=T.INV(1-AG16/2,AG14)/SQRT(T.INV(1-AG16/2,AG14)^2+AG14)

r Critical = 0.7545

r_{s} is not significant at α = 0.05 because r_{s} (0.7339) is less than r Critical (0.7545). This r_{s} would be significant at α = 0.10 but not at α = 0.05.

__Method 2 – Compare the p value to Alpha__

*(Click On Image To See Larger Version)*

*(Click On Image To See Larger Version)*

p Value is calculated in cell BS29 by the following formula:

=1-F.DIST(((AG14*S15^2)/(1-S22^2)),1,AG14,TRUE) = 0.0536

r_{s} is not significant at α = 0.05 because the p Value (0.0536) is greater than Alpha (0.05). This r_{s} would be significant at α = 0.10 but not at α = 0.05.

### Two Different Methods Used to Calculate r_{s} Critical Values

There is slight disagreement in the statistical community about how to calculate r_{s} Critical Values.

Some use a table of Critical r_{s} values. This table of values was created in 1938 in the journal *The Annals of Mathematical Statistics*.

Others use the formula for Critical r_{s} as was done in this example. This formula is once again shown here as follows:

*(Click On Image To See Larger Version)*

The results are quite close. As sample size increase, the results of both methods converge. This is shown in the following comparison with α set to 0.05:

*(Click On Image To See Larger Version)*

**Excel Master Series Blog Directory**

Statistical Topics and Articles In Each Topic

- Histograms in Excel
- Bar Chart in Excel
- Combinations & Permutations in Excel
- Normal Distribution in Excel
- Overview of the Normal Distribution
- Normal Distribution’s PDF (Probability Density Function) in Excel 2010 and Excel 2013
- Normal Distribution’s CDF (Cumulative Distribution Function) in Excel 2010 and Excel 2013
- Solving Normal Distribution Problems in Excel 2010 and Excel 2013
- Overview of the Standard Normal Distribution in Excel 2010 and Excel 2013
- An Important Difference Between the t and Normal Distribution Graphs
- The Empirical Rule and Chebyshev’s Theorem in Excel – Calculating How Much Data Is a Certain Distance From the Mean
- Demonstrating the Central Limit Theorem In Excel 2010 and Excel 2013 In An Easy-To-Understand Way

- t-Distribution in Excel
- Binomial Distribution in Excel
- z-Tests in Excel
- Overview of Hypothesis Tests Using the Normal Distribution in Excel 2010 and Excel 2013
- One-Sample z-Test in 4 Steps in Excel 2010 and Excel 2013
- 2-Sample Unpooled z-Test in 4 Steps in Excel 2010 and Excel 2013
- Overview of the Paired (Two-Dependent-Sample) z-Test in 4 Steps in Excel 2010 and Excel 2013

- t-Tests in Excel
- Overview of t-Tests: Hypothesis Tests that Use the t-Distribution
- 1-Sample t-Tests in Excel
- 1-Sample t-Test in 4 Steps in Excel 2010 and Excel 2013
- Excel Normality Testing For the 1-Sample t-Test in Excel 2010 and Excel 2013
- 1-Sample t-Test – Effect Size in Excel 2010 and Excel 2013
- 1-Sample t-Test Power With G*Power Utility
- Wilcoxon Signed-Rank Test in 8 Steps As a 1-Sample t-Test Alternative in Excel 2010 and Excel 2013
- Sign Test As a 1-Sample t-Test Alternative in Excel 2010 and Excel 2013

- 2-Independent-Sample Pooled t-Tests in Excel
- 2-Independent-Sample Pooled t-Test in 4 Steps in Excel 2010 and Excel 2013
- Excel Variance Tests: Levene’s, Brown-Forsythe, and F Test For 2-Sample Pooled t-Test in Excel 2010 and Excel 2013
- Excel Normality Tests Kolmogorov-Smirnov, Anderson-Darling, and Shapiro Wilk Tests For Two-Sample Pooled t-Test
- Two-Independent-Sample Pooled t-Test - All Excel Calculations
- 2- Sample Pooled t-Test – Effect Size in Excel 2010 and Excel 2013
- 2-Sample Pooled t-Test Power With G*Power Utility
- Mann-Whitney U Test in 12 Steps in Excel as 2-Sample Pooled t-Test Nonparametric Alternative in Excel 2010 and Excel 2013
- 2- Sample Pooled t-Test = Single-Factor ANOVA With 2 Sample Groups

- 2-Independent-Sample Unpooled t-Tests in Excel
- 2-Independent-Sample Unpooled t-Test in 4 Steps in Excel 2010 and Excel 2013
- Variance Tests: Levene’s Test, Brown-Forsythe Test, and F-Test in Excel For 2-Sample Unpooled t-Test
- Excel Normality Tests Kolmogorov-Smirnov, Anderson-Darling, and Shapiro-Wilk For 2-Sample Unpooled t-Test
- 2-Sample Unpooled t-Test Excel Calculations, Formulas, and Tools
- Effect Size for a 2-Independent-Sample Unpooled t-Test in Excel 2010 and Excel 2013
- Test Power of a 2-Independent Sample Unpooled t-Test With G-Power Utility

- Paired (2-Sample Dependent) t-Tests in Excel
- Paired t-Test in 4 Steps in Excel 2010 and Excel 2013
- Excel Normality Testing of Paired t-Test Data
- Paired t-Test Excel Calculations, Formulas, and Tools
- Paired t-Test – Effect Size in Excel 2010, and Excel 2013
- Paired t-Test – Test Power With G-Power Utility
- Wilcoxon Signed-Rank Test in 8 Steps As a Paired t-Test Alternative
- Sign Test in Excel As A Paired t-Test Alternative

- Hypothesis Tests of Proportion in Excel
- Hypothesis Tests of Proportion Overview (Hypothesis Testing On Binomial Data)
- 1-Sample Hypothesis Test of Proportion in 4 Steps in Excel 2010 and Excel 2013
- 2-Sample Pooled Hypothesis Test of Proportion in 4 Steps in Excel 2010 and Excel 2013
- How To Build a Much More Useful Split-Tester in Excel Than Google's Website Optimizer

- Chi-Square Independence Tests in Excel
- Chi-Square Goodness-Of-Fit Tests in Excel
- F Tests in Excel
- Correlation in Excel
- Pearson Correlation in Excel
- Spearman Correlation in Excel
- Confidence Intervals in Excel
- z-Based Confidence Intervals of a Population Mean in 2 Steps in Excel 2010 and Excel 2013
- t-Based Confidence Intervals of a Population Mean in 2 Steps in Excel 2010 and Excel 2013
- Minimum Sample Size to Limit the Size of a Confidence interval of a Population Mean
- Confidence Interval of Population Proportion in 2 Steps in Excel 2010 and Excel 2013
- Min Sample Size of Confidence Interval of Proportion in Excel 2010 and Excel 2013

- Simple Linear Regression in Excel
- Overview of Simple Linear Regression in Excel 2010 and Excel 2013
- Complete Simple Linear Regression Example in 7 Steps in Excel 2010 and Excel 2013
- Residual Evaluation For Simple Regression in 8 Steps in Excel 2010 and Excel 2013
- Residual Normality Tests in Excel – Kolmogorov-Smirnov Test, Anderson-Darling Test, and Shapiro-Wilk Test For Simple Linear Regression
- Evaluation of Simple Regression Output For Excel 2010 and Excel 2013
- All Calculations Performed By the Simple Regression Data Analysis Tool in Excel 2010 and Excel 2013
- Prediction Interval of Simple Regression in Excel 2010 and Excel 2013

- Multiple Linear Regression in Excel
- Basics of Multiple Regression in Excel 2010 and Excel 2013
- Complete Multiple Linear Regression Example in 6 Steps in Excel 2010 and Excel 2013
- Multiple Linear Regression’s Required Residual Assumptions
- Normality Testing of Residuals in Excel 2010 and Excel 2013
- Evaluating the Excel Output of Multiple Regression
- Estimating the Prediction Interval of Multiple Regression in Excel
- Regression - How To Do Conjoint Analysis Using Dummy Variable Regression in Excel

- Logistic Regression in Excel
- Logistic Regression Overview
- Logistic Regression in 6 Steps in Excel 2010 and Excel 2013
- R Square For Logistic Regression Overview
- Excel R Square Tests: Nagelkerke, Cox and Snell, and Log-Linear Ratio in Excel 2010 and Excel 2013
- Likelihood Ratio Is Better Than Wald Statistic To Determine if the Variable Coefficients Are Significant For Excel 2010 and Excel 2013
- Excel Classification Table: Logistic Regression’s Percentage Correct of Predicted Results in Excel 2010 and Excel 2013
- Hosmer- Lemeshow Test in Excel – Logistic Regression Goodness-of-Fit Test in Excel 2010 and Excel 2013

- Single-Factor ANOVA in Excel
- Overview of Single-Factor ANOVA
- Single-Factor ANOVA in 5 Steps in Excel 2010 and Excel 2013
- Shapiro-Wilk Normality Test in Excel For Each Single-Factor ANOVA Sample Group
- Kruskal-Wallis Test Alternative For Single Factor ANOVA in 7 Steps in Excel 2010 and Excel 2013
- Levene’s and Brown-Forsythe Tests in Excel For Single-Factor ANOVA Sample Group Variance Comparison
- Single-Factor ANOVA - All Excel Calculations
- Overview of Post-Hoc Testing For Single-Factor ANOVA
- Tukey-Kramer Post-Hoc Test in Excel For Single-Factor ANOVA
- Games-Howell Post-Hoc Test in Excel For Single-Factor ANOVA
- Overview of Effect Size For Single-Factor ANOVA
- ANOVA Effect Size Calculation Eta Squared in Excel 2010 and Excel 2013
- ANOVA Effect Size Calculation Psi – RMSSE – in Excel 2010 and Excel 2013
- ANOVA Effect Size Calculation Omega Squared in Excel 2010 and Excel 2013
- Power of Single-Factor ANOVA Test Using Free Utility G*Power
- Welch’s ANOVA Test in 8 Steps in Excel Substitute For Single-Factor ANOVA When Sample Variances Are Not Similar
- Brown-Forsythe F-Test in 4 Steps in Excel Substitute For Single-Factor ANOVA When Sample Variances Are Not Similar

- Two-Factor ANOVA With Replication in Excel
- Two-Factor ANOVA With Replication in 5 Steps in Excel 2010 and Excel 2013
- Variance Tests: Levene’s and Brown-Forsythe For 2-Factor ANOVA in Excel 2010 and Excel 2013
- Shapiro-Wilk Normality Test in Excel For 2-Factor ANOVA With Replication
- 2-Factor ANOVA With Replication Effect Size in Excel 2010 and Excel 2013
- Excel Post Hoc Tukey’s HSD Test For 2-Factor ANOVA With Replication
- 2-Factor ANOVA With Replication – Test Power With G-Power Utility
- Scheirer-Ray-Hare Test Alternative For 2-Factor ANOVA With Replication

- Two-Factor ANOVA Without Replication in Excel
- Randomized Block Design ANOVA in Excel
- Repeated-Measures ANOVA in Excel
- Single-Factor Repeated-Measures ANOVA in 4 Steps in Excel 2010 and Excel 2013
- Sphericity Testing in 9 Steps For Repeated Measures ANOVA in Excel 2010 and Excel 2013
- Effect Size For Repeated-Measures ANOVA in Excel 2010 and Excel 2013
- Friedman Test in 3 Steps For Repeated-Measures ANOVA in Excel 2010 and Excel 2013

- ANCOVA in Excel
- Normality Testing in Excel
- Creating a Box Plot in 8 Steps in Excel
- Creating a Normal Probability Plot With Adjustable Confidence Interval Bands in 9 Steps in Excel With Formulas and a Bar Chart
- Chi-Square Goodness-of-Fit Test For Normality in 9 Steps in Excel
- Kolmogorov-Smirnov, Anderson-Darling, and Shapiro-Wilk Normality Tests in Excel

- Nonparametric Testing in Excel
- Mann-Whitney U Test in 12 Steps in Excel
- Wilcoxon Signed-Rank Test in 8 Steps in Excel
- Sign Test in Excel
- Friedman Test in 3 Steps in Excel
- Scheirer-Ray-Hope Test in Excel
- Welch's ANOVA Test in 8 Steps Test in Excel
- Brown-Forsythe F Test in 4 Steps Test in Excel
- Levene's Test and Brown-Forsythe Variance Tests in Excel
- Chi-Square Independence Test in 7 Steps in Excel
- Chi-Square Goodness-of-Fit Tests in Excel
- Chi-Square Population Variance Test in Excel

- Post Hoc Testing in Excel
- Creating Interactive Graphs of Statistical Distributions in Excel
- Interactive Statistical Distribution Graph in Excel 2010 and Excel 2013
- Interactive Graph of the Normal Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Chi-Square Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the t-Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the t-Distribution’s PDF in Excel 2010 and Excel 2013
- Interactive Graph of the t-Distribution’s CDF in Excel 2010 and Excel 2013
- Interactive Graph of the Binomial Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Exponential Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Beta Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Gamma Distribution in Excel 2010 and Excel 2013
- Interactive Graph of the Poisson Distribution in Excel 2010 and Excel 2013

- Solving Problems With Other Distributions in Excel
- Solving Uniform Distribution Problems in Excel 2010 and Excel 2013
- Solving Multinomial Distribution Problems in Excel 2010 and Excel 2013
- Solving Exponential Distribution Problems in Excel 2010 and Excel 2013
- Solving Beta Distribution Problems in Excel 2010 and Excel 2013
- Solving Gamma Distribution Problems in Excel 2010 and Excel 2013
- Solving Poisson Distribution Problems in Excel 2010 and Excel 2013

- Optimization With Excel Solver
- Maximizing Lead Generation With Excel Solver
- Minimizing Cutting Stock Waste With Excel Solver
- Optimal Investment Selection With Excel Solver
- Minimizing the Total Cost of Shipping From Multiple Points To Multiple Points With Excel Solver
- Knapsack Loading Problem in Excel Solver – Optimizing the Loading of a Limited Compartment
- Optimizing a Bond Portfolio With Excel Solver
- Travelling Salesman Problem in Excel Solver – Finding the Shortest Path To Reach All Customers

- Chi-Square Population Variance Test in Excel
- Analyzing Data With Pivot Tables
- SEO Functions in Excel
- Time Series Analysis in Excel
- VLOOKUP

## No comments:

## Post a Comment