What is Regression Analysis?

The regression analysis is a statistical method that can estimate the relationship between two or more variables. This method can provide a better understanding of how the value of the dependent variable changes, when one of the independent variables change. The analysis assumes that other independent variables remain constant when running the calculations for working a variable.

In essence, a dependent variable is the outcome you are trying to analyze and predict, whereas an independent variable, also known as regressor, is the inputs that affects the dependent variable(s).

Regression analysis can be done using various techniques. Excel can solve linear regression analysis problems using the least squares method. Linear regression method assumes a linear correlation between independent and dependent variables by the formula;

y = bx + a

y: dependent value
x: independent value
b: the slope of the regression line
a: Y-intercept, a point where the regression line intersects the y-axis

In this article, we're going to be using a sample data set to go over different methods. You can download the workbook below.

Download Workbook

In Excel

You can create a regression analysis in Excel using any of these three methods:

Analysis ToolPak add-in
Formulas
Regression Graph

Analysis ToolPak

The Analysis ToolPak add-in is a very useful tool that shines in data analysis. It is a ‘hidden’ add-in, meaning that it’s not active in Excel by default. You can activate it from the Add-Ins dialog. To actiave it, follow these steps:

Go to FILE > Options > Add-Ins.
Select Excel Add-ins in the Manage dropdown and click the Go
Select the Analysis ToolPak and click OK.

Add-in will be placed under the DATA tab with the name of Data Analysis after activation. Begin by going into this dialog. Here, select the Regression option and click the OK button to open the Regression dialog.

In the Regression dialog, you need to specify the references of ranges containing the X and Y values. The X and Y values are independent and dependent variables respectively. To build a multiple regression model, use two or more adjacent columns for independent variables (X).

Before clicking OK button to create analysis output, let’s go over the other options in the dialog:

Labels: Check whether your data has titles in the first row.
Constant is Zero: Check whether dependent value should be equal to 0 when the independent value equals 0.
Confidence Level: Check and modify the percentage value to apply a custom confidence level.
Output options: Choose where analysis is to be placed.
Residuals: Check the residual values (the difference between the predicted and actual values you want to see in the output). Plots will also give you a chart.
Normal Probability Plots: Check to see the normal probability information to the regression analysis results on a chart.

Excel will generate multiple tables and charts based on the options you choose. These tables are placed under 3 sections:

Summary Output contains basic statistics about regression, ANOVA (analysis of variance) information, and information about the regression line.
Residual Output shows you how far away the actual data points are from the predicted data points.
Probability Output contains the normal distribution of regression analysis results.

Note: Even though the Regression tool in the Data Analysis can provide an analysis of the results, it is not dynamic, and you have to open the Regression dialog every time when using a new set of data.

Commonly Used Formulas

You can use formulas to reach certain values when performing a simple regression analysis. Excel’s statistical functions like these will provide useful when creating reports:

LINEST
SLOPE
INTERCEPT
CORREL

Let’s go over each of these functions and see how they work on a few examples. The LINEST function calculates a straight line that best fits your data using the "least squares" method, and returns an array describing that line. It takes 4 arguments, two of which are the array of values that contain the independent and dependent variables. The remaining two arguments are optional TRUE/FALSE arguments for the Constant is Zero assumption, and if additional regression stats are to be calculated.

Using a function with X and Y values will return slope and Y-intercept values of the regression equation y = bx + a. To return both values, you need to select 2 adjacent cells, and press the Ctrl + Alt + Enter combination when entering the formula.

For example,

=LINEST(B2:B21,A2:A21)

Note: These results are the Coefficient numbers in the Summary Output of Regression tool.

The SLOPE and INTERCEPT functions return the same slope and Y-intercept values. If you do not want to use array functions, you can use these functions instead. Aside from the LINEST, these functions do not have any other optional arguments.

=SLOPE(B2:B21,A2:A21)

=INTERCEPT(B2:B21,A2:A21)

The CORREL function returns the correlation coefficient of two arrays. You can use the correlation coefficient to determine how strongly the two variables are related to each other. This value can also be shown in an analysis created with the Regression tool. This coefficient is named Multiple R.

You can generate residual values using the slope and Y-intercept. Based on the y = bx + a formula, you need to multiply the slope with the X value, and add the Y-intercept. The difference between the actual Y and predicted Y gives the residual value.

Assume that we call the slope and intercept values “slope” and “y_intercept”. Our formula for the first predicted value becomes:

=A2*slope+y_intercept

The formula to find the difference between predicted and actual value would be,

=B2-G2

We then copy down both formulas to generate the values for each value in our data table.

Regression Graph

Visualizing data is one of the easiest way to identify outliers and compare multiple elements. Excel versions 2010 or newer support Trendlines. This is a powerful tool that can show the regression between two series without any calculations.

Make sure that the independent variables (X) are on the first column, and dependent variables (Y) are on the second. Then, select your data table to start creating a chart.

Create a Scatter Chart from INSERT > Scatter (Charts)

Add a linear Trendline by using the plus sign next to chart, or right-click a data point.

The trendline formula does the same thing as the Regression tool and Formula methods. You can also improve the looks with few modifications. Double-click on the trendline to open options pane.

You can add an Equation Formula and R² values to the chart by enabling the related options in the Format Trendline menu.