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).
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. Select the first scatter graph with just dots and no lines.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.Look for the icon of a graph with just dots on it.Then, in Excel, select both columns of data by selecting and holding on the top-left number and dragging down to the bottom-most number in the right column.
You can copy and paste the data into Excel so you can play along. Our example will have Time in years and Stock Value in dollars. Then we can do some neat things with the trendline and see what it means. The first step is to create a scatter plot. Let’s assume you haven’t learned all about Excel yet. How To Create An Excel Scatter Plot With Linear Regression Trendline Now we know those words are actually English and what they mean. That line is a simple linear regression trendline through a scatter plot. Could we draw a line through the dots that would show a trend? Let’s call that a trendline.