Spearman correlation is primarily used for:

• Ordinal data: When variables are rankings or ordered categories
• Non-linear monotonic relationships: When variables increase/decrease together but not necessarily in a straight line
• Data with outliers: Spearman is robust to extreme values
• Small sample sizes: Works with as few as 4 pairs of data
• Non-normal distributions: Doesn’t require normal distribution assumptions
• Non-parametric testing: When parametric assumptions can’t be met

The Spearman correlation coefficient (ρ) uses a scale from -1 to +1:

• +1.00: Perfect positive monotonic relationship (as X increases, Y always increases)
• 0.70 to 0.99: Strong positive relationship
• 0.30 to 0.69: Moderate positive relationship
• 0.00 to 0.29: Weak or no relationship
• -0.01 to -0.29: Weak negative relationship
• -0.30 to -0.69: Moderate negative relationship
• -0.70 to -0.99: Strong negative relationship
• -1.00: Perfect negative monotonic relationship (as X increases, Y always decreases)

The choice depends on your data characteristics:

Use Pearson correlation when:

• Both variables are continuous and normally distributed
• You specifically want to measure linear relationships
• Data has no significant outliers
• Sample size is adequate (n ≥ 30)
• All parametric assumptions are met

Use Spearman correlation when:

• Variables are ordinal or not normally distributed
• You want to measure monotonic (not necessarily linear) relationships
• Data contains outliers
• Sample size is small
• Parametric assumptions are violated
• You need a more robust measure

Step-by-step calculation (without ties):

1. Rank both variables separately (assign 1 to smallest value)
2. Calculate differences between ranks for each pair: d = rank(X) – rank(Y)
3. Square each difference: d²
4. Sum all squared differences: Σd²
5. Count number of pairs: n
6. Apply formula: ρ = 1 – [6Σd² / (n(n² – 1))]

With tied ranks:

1. Assign average ranks for tied values
2. Calculate Pearson correlation on the ranks
3. Use formula: ρ = Σ(Rₓ – R̄ₓ)(Rᵧ – R̄ᵧ) / √[Σ(Rₓ – R̄ₓ)² Σ(Rᵧ – R̄ᵧ)²]

For Pearson correlation:

• Use =CORREL(array1, array2)
• Or =PEARSON(array1, array2) (identical to CORREL)
• Array1 and array2 are your data ranges

For Spearman correlation:

• Method 1: =CORREL(RANK.AVG(array1), RANK.AVG(array2))
• Method 2: Use Data Analysis Toolpak → Correlation
• Method 3: Manual calculation using RANK.AVG and CORREL

For p-value calculation:

• Use t-test: =T.DIST.2T(ABS(t), df) where t = r√(n-2)/√(1-r²)
• Or use Data Analysis Toolpak for comprehensive results

Spearman correlation significance indicates whether the observed correlation is statistically different from zero (no correlation). Significance is determined by:

1. P-value approach:

• Calculate p-value using t-test: t = ρ√(n-2)/√(1-ρ²)
• Compare p-value with significance level (α), typically 0.05
• If p < 0.05, correlation is statistically significant

2. Critical value approach:

• Compare calculated t-value with critical t-value from t-distribution table
• Degrees of freedom = n – 2
• If |t| > t_critical, correlation is significant

3. Direct interpretation (for n ≤ 30):

• Use Spearman’s critical values table specific to sample size
• Compare calculated ρ with critical ρ values
• If |ρ| > ρ_critical, correlation is significant

Note: Significance doesn’t indicate strength of relationship, only that it’s unlikely to occur by chance.

Key differences:

When values differ:

• If ρ > r: Relationship is monotonic but not perfectly linear
• If r > ρ: Relationship is linear but contains outliers affecting ranks
• If both are similar: Relationship is approximately linear