Data analysis excel template 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
0 kommentar(er)
