This is called multiple linear regression (i.e. In some cases, there is more than one independent variable which should be considered when creating a linear regression model. The following data table comes from the Microsoft Support website LINEST page. We can check this by entering x= 14 in the equation of the line, y =2x + 0.įorecasting with LINEST (Multiple Linear Regression) = SUM ( LINEST ( C3 : C7, B3 : B7 )* specifies the independent variable to be used for predicting the dependent variable, in this case, 14.
Below is an example showing what the y value will be when x=14. Forecasting with LINEST (Simple Regression)Ĭombining the LINEST and SUM functions can be used to predict the value of a dependent variable y, given known x and y data. Simply enter the formula and click enter and the resultant cells will “spill out” into the array.įor the remainder of this article we will reference using LINEST with respect to dynamic arrays in Microsoft 365 Excel. It is no longer required to use CTRL + SHIFT + ENTER or highlight the area of cells the array will take up. In releases following January 2020 of Excel in Microsoft 365 (previously Office 365), dynamic arrays have changed the way array formulas are evaluated. A low r 2 value would mean a poor correlation between your dependent and independent variables, and the opposite is true for high r 2 values, with r 2 = 1 being a perfect fit. It can be thought of as an indicator of fit. The r 2 value is an indicator of the strength of the correlation of the model. The highlighted cells show the slope = 2, intercept = 0 and r 2 = 1. Using the above example and selecting TRUE for the stats parameter: The main statistics to understand are the slope coefficients, y-intercept and the coefficient of determination or the r 2 value of the model. The F statistic (to determine if the variables’ relationship occurs by chance) Standard error for each slope coefficient You may be wondering what each variable means. When the stats option is set to TRUE, the organization of the regression statistics are as follows:
= LINEST ( B3 : B7, C3 : C7, TRUE, FALSE )
0 kommentar(er)
