interpreting excel data analysis regression output

#INTERPRETING EXCEL DATA ANALYSIS REGRESSION OUTPUT HOW TO#

Regular formula (entered by pressing Enter): With the x value in E2 and the constant 1 in F2, either of the below formulas will work a treat:

In this case, you need to input the 1 constant in some cell too because you cannot mix references and values in an array constant. Instead of hardcoding the x value in the formula, you can provide it as a cell reference. For this, you use LINEST in combination with the SUM or SUMPRODUCT function.įor example, here's how you can calculate the number of umbrella sales for the next month, say October, based on sales in the previous months and October's advertising budget of $50: Simple linear regression: predict dependent variableĪpart from calculating the a and b values for the regression equation, the Excel LINEST function can also estimate the dependent variable (y) based on the known independent variable (x). To predict the sales number, we supply the values returned by the LINEST formula to the multiple regression equation:įor example, with $50 spent on advertising and an average monthly rainfall of 100 mm, you are expected to sell approximately 23 umbrellas: Please note that the multiple regression formula returns the slope coefficients in the reverse order of the independent variables (from right to left), that is b n, b n-1, …, b 2, b 1: In case you have two or more independent variables, be sure to input them in adjacent columns, and supply that whole range to the known_x's argument.įor example, with sales numbers ( y values) in D2:D13, advertising cost (one set of x values) in B2:B13 and average monthly rainfall (another set of x values) in C2:C13, you use this formula:Īs formula is going to return an array of 3 values (2 slope coefficients and the intercept constant), we select three contiguous cells in the same row, enter the formula and press the Ctrl + Shift + Enter shortcut. Multiple linear regression: slope and intercept The slope and intercept values can also be obtained separately by using the corresponding function or by nesting the LINEST formula into INDEX:Īs shown in the screenshot below, all three formulas yield the same results: Supply the above values to a simple linear regression equation, and you will get the following formula to predict the sales number based on the advertising cost:įor example, if you spend $50 on advertising, you are expected to sell 21 umbrellas: If plotted on a graph, it is the value at which the regression line crosses the y-axis. It means that when x increases by 1, y increases by 0.52. The slope is approximately 0.52 (rounded to two decimal places).

The formula will return the slope coefficient in the first cell (E2) and the intercept constant in the second cell (F2): To enter it correctly in your worksheet, select two adjacent cells in the same row, E2:F2 in this example, type the formula, and press Ctrl + Shift + Enter to complete it. The last two arguments can be set to TRUE or omitted.įor example, with y values (sales numbers) in C2:C13 and x values (advertising cost) in B2:B13, our linear regression formula is as simple as: To get the intercept and the slope of a regression line, you use the LINEST function in its simplest form: supply a range of the dependent values for the known_y's argument and a range of the independent values for the known_x's argument. Below, you will find a few examples of using LINEST formulas in Excel that will hopefully help to sink the theoretical knowledge in :) Simple linear regression: calculate slope and intercept The LINEST function might be tricky to use, especially for novices, because you should not only build a formula correctly, but also properly interpret its output.

#INTERPRETING EXCEL DATA ANALYSIS REGRESSION OUTPUT HOW TO#

How to use LINEST in Excel - formula examples It is the default behavior of the LINEST function, but if you'd like to hide the error notations, wrap your LINEST formula into IFERROR as shown in this example. In the last three rows, the #N/A errors will appear in the third and subsequent columns that are not filled with data. The below map shows the order in which LINEST returns an array of statistics:

Measures the amount of variance in the dependent variable that is not explained by your regression model. Indicates how much of the variation in the dependent variable is explained by the model. It is used to do the F-test for the null hypothesis to determine the overall goodness of fit of the model. Shows the precision of the regression analysis. Indicates how well the regression equation explains the relationship among the variables. The standard error value for the constant a. The standard error value(s) for the b coefficient(s). The stats argument set to TRUE instructs the LINEST function to return the following statistics for your regression analysis: Statistic If it is entered as a regular formula, only the first slope coefficient is returned. Since LINEST returns an array of values, it must be entered as an array formula by pressing the Ctrl + Shift + Enter shortcut.

Interpreting excel data analysis regression output