This SPSS Excel tutorial explains how to calculate Covariance, Correlation and Least Squares Method in Excel and SPSS.
Covariance and Correlation
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.
Covariance
The below formula is for calculation of Population Covariance. For Sample Covariance, divide n-1 instead of N.
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
Move x and y to the right hand side
Click on Option, and then check the box Cross-product deviations and covariances
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.
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.
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
Select y as Dependent variable and x as Independent variable, click on OK
In the output, one of the table is called Coefficients, where you can find the constant is 0.15 and slope is 0.14
