Correlation and Regression

This article describes the formula syntax and usage of the PEARSON function in Microsoft Excel.

When data is not normally distributed or when the presence of outliers gives a distorted picture of the association between two random variables, the Spearman’s rank correlation is a non-parametric test that can be used instead of the Pearson’s correlation coefficient.

Use curvilinear regression when you have graphed two measurement variables and you want to fit an equation for a curved line to the points on the graph.

Regression is a way to find out the “best fit” equation for a set of data. It can help with forecasting and modeling. The linear regression equation will enable you to duplicate the overall data, but it won’t tell you how closely that data is related. An r squared value (the coefficient of determination) tells you how closely the linear regression line matches the dataset.

These are the “Goodness of Fit” measures. They tell you how well the calculated linear regression equation fits your data.

Full explanation of the process and modifying the output from Excel.

Although Excel is capable of calculating a number of descriptive and inferential statistics for you, it is often better to show a visual representation of data when presenting information to a group. Using Excel’s built in trendline function, you can add a linear regression trendline to any Excel scatter plot.

In this section we show how to use dummy variables to model categorical variables using linear regression in a way that is similar to that employed in Dichotomous Variables and the t-test.