SPSS Excel Covariance, Correlation, Least Squares Method

This SPSS Excel tutorial explains how to calculate Covariance, Correlation and Least Squares Method in Excel and SPSS.

Covariance and Correlation

spss excel correlation 01

When we plot a scattered graph to see the relationship between two interval variables, we can see how straight a line is formed (whether the relationship is strong) and whether the relationship is positive or negative (upward or download).

However, when we want to look deeper to see the strength of the relationship, we need to calculate covariance and correlation. Covariance is a process to help us find the Correlation, which is the number we ultimately need for the strength.


The below formula is for calculation of Population Covariance. For Sample Covariance, divide n-1 instead of N.

population covariance

While σx is denoted as standard variation of x, σxy is denoted as Covariance.

After we calculate the covariance, we can check the sign whether it is negative or positive.  Positive covariance means positive relationship (y increases as x increases), negative covariance means a negative relationship (y decreases as x increases). However, we cannot see the strength of relationship at this point.

Calculate Covariance in Excel

For sample covariance

=COVARIANCE.S(array1, array2)

For population covariance

=COVARIANCE.P(array1, array2)

Calculate Covariance in SPSS

Note that SPSS can only calculate sample Covariance.

Navigate to Analyze > Correlate > Bivariate

spss covariance 01

Move x and y to the right hand side

spss covariance 02

Click on Option, and then check the box Cross-product deviations and covariances

spss covariance 03


In the output table, 26.164 is the sample covariance. 1897.659 on the left is the variance of x.

The significance level (2 tailed) < 0.05 means there is a statistically significant correlation between two variables.

spss covariance 04

Coefficient of Correlation

Again, Covariance is just a step to calculate correlation. The next step is to calculate Coefficient of Correlation using Covariance.

Formula of Population coefficient of correlation:  (σ is the standard deviation)

ρ = σxy / (σx * σy)

Sample coefficient of correlation:

r = Sxy / (Sx * Sy)

The calculated result of Coefficient of Correlation ranges between -1 and 1. The positive sign indicates positive relationship while negative sign indicates negative relationship.

In addition, 1 indicates the strength of linear relationship is very strong, 0 indicates no linear relationship.

Calculate Coefficient of Correlation in Excel

=CORREL(array x, array y)

Calculate Coefficient of Correlation in SPSS

You can find the Correlation in the above output table.

spss correlation 01

Least Squares Method

Least Squares Method is to find the linear equation of best fit line for the x and y.

The equation of best fit line is

mean of y = slope * (mean of x) + constant

To create the equation, first calculate the slope of the straight line

slope = covariance x y / variance of x

Afterwards, substitute mean of x and mean of y to find constant.

Calculate slope in Excel using Slope Function

=SLOPE(array y, array x)

or calculate indirectly

=COVARIANCE.S(array x, array y)/VAR.S(array y)

Calculate constant in Excel using Intercept Function

=INTERCEPT(array y, array x)

Calculate slope and constant in SPSS

Navigate to Analyze > Regression > Linear

spss linear regression 01

Select y as Dependent variable and x as Independent variable, click on OK

spss linear regression 02

In the output, one of the table is called Coefficients, where you can find the constant is 0.15 and slope is 0.14

spss linear regression 03


Leave a Reply

Your email address will not be published.