aseboview.blogg.se

• REGRESSION EXCEL FORMULA HOW TO

One plot is generated for each independent variable.The plot in Figure 7 shows that the data is a reasonable fit with the normal assumption. In fact except for the scale it generates the same plot as the QQ plot generated by the supplemental data analysis tool (switching the axes). It plays the same role as the QQ plot.Since the p-value = 0.00026 Analysis|Trendline and choosing Linear Trendline. We can also use the Regression data analysis tool to produce the output in Figure 3.įigure 3 – Output from Regression data analysis tool 33.7% of the variance in the poverty rate is explained by the model), the standard error of the estimate is 2.47, etc. Here Poverty represents the predicted value.

The remaining three rows have two values each, labeled on the left and the right.

The column headings b 1, b 2, b 3 and intercept refer to the first two rows only (note the order of the coefficients). As we can see from Figure 2, the model predicts a poverty rate of 12.87% when infant mortality is 7.0, whites make up 80% of the population and violent crime is 400 per 100,000 people.įigure 2 also shows the output from LINEST after we highlight the shaded range H13:K17 and enter =LINEST(B4:B53,C4:E53,TRUE,TRUE). Highlighting the range J6:J8, we enter the array formula =TREND(B4:B53,C4:E53,G6:I8). Here we show the data for the first 15 of 50 states (columns A through E) and the percentage of poverty forecasted when infant mortality, percentage of whites in the population and crime rate are as indicated (range G6:J8).

REGRESSION EXCEL FORMULA HOW TO

We illustrate how to use TREND and LINEST in Figure 2.įigure 2 – TREND and LINEST for data in Example 1 Poverty (predicted) = b 0 + b 1 ∙ Infant + b 2 ∙ White + b 3 ∙ Crime. We need to find the parameters b 0 , b 1 and such that The Regression data analysis tool works exactly as in the simple linear regression case, except that additional charts are produced for each of the independent variables.Įxample 2: We revisit Example 1 of Multiple Correlation, analyzing the model in which the poverty rate can be estimated as a linear combination of the infant mortality rate, the percentage of the population that is white and the violent crime rate (per 100,000 people). As before, you need to manually add the appropriate labels for clarity. Thus for a model with 3 independent variables you need to highlight an empty 5 × 4 region. LINEST works just as in the simple linear regression case, except that instead of using a 5 × 2 region for the output a 5 × k region is required where k = the number of independent variables + 1. TREND works exactly as described in Method of Least Squares, except that the second parameter R2 will now contain data for all the independent variables. In particular, the standard error of the intercept b 0 (in cell K9) is expressed by the formula =SQRT(I17), the standard error of the color coefficient b 1 (in cell K10) is expressed by the formula =SQRT(J18), and the standard error of the quality coefficient b 2 (in cell K11) is expressed by the formula =SQRT(K19).Įxcel Functions: The functions SLOPE, INTERCEPT, STEYX and FORECAST don’t work for multiple regression, but the functions TREND and LINEST do support multiple regression as does the Regression data analysis tool. Then just as in the simple regression case SS Res = DEVSQ(O4:O14) = 277.36, df Res = n – k – 1 = 11 – 2 – 1 = 8 and MS Res = SS Res/ df Res= 34.67 (see Multiple Regression Analysis for more details).īy the Observation following Property 4 it follows that MS Res ( X T X) -1 is the covariance matrix for the coefficients, and so the square root of the diagonal terms are the standard error of the coefficients. First calculate the array of error terms E (range O4:O14) using the array formula I4:I14 – M4:M14. The standard error of each of the coefficients in B can be calculated as follows. Y-hat, can then be calculated using the array formula Per Property 1 of Multiple Regression using Matrices, the coefficient vector B (in range K4:K6) can be calculated using the array formula: The matrix ( X T X) -1 in range E17:G19 can be calculated using the array formula Range E4:G14 contains the design matrix X and range I4:I14 contains Y.

Example 1: Calculate the linear regression coefficients and their standard errors for the data in Example 1 of Least Squares for Multiple Regression (repeated below in Figure using matrix techniques.įigure 1 – Creating the regression line using matrix techniques