excel, regression modelling, data mining

Data Mining: Use data 15 This worksheet contains the following cells: a. Source data in column A b. Predictors X4, X3, X2 and X1 in columns C to F c. Unit vector, whose elements are 1.0, in column G d. Target values in column H e. Linear regression coefficients in cells J3:N3 f. Regression model coefficients in cells P3:P7 g. Forecasting model output in column R h. A chart showing the Target and forecasting values, and i. Root mean square error in cell K43 3. Given your group number, find a data column and copy its cells 1:51 from [login to view URL] to Source Data cells. Place the data values into Predictors cells X4 – X1 as follows: Predictor X4 Predictor X3 Predictor X2 Predictor X1 Unit Target Y Value 1 Value 2 Value 3 Value 4 1 Value 5 Value 2 Value 3 Value 4 Value 5 1 Value 6 … … Value 47 Value 48 Value 49 Value 50 1 Value 51 4. Place the Target in column H of the worksheet. 5. Read about LINEST function that computes the coefficients of a linear regression model (b, m1, m2, and m3 introduced in the lecture) in the Excel Help or in [login to view URL] file. 6. You can use LINEST as follows: =LINEST(H3:H49, C3:F49, TRUE, TRUE) where H3:H49 are the Target values, C3:F49 are the Predictors, and the TRUE values for using the Unit vector and statistics. You can enter this formula in the formula bar, having selected a set of 5x5 cells J3:N7. Press Ctrl+Shift+Enter to compute the formula, and show the regression model coefficients in the cells J3:N3. 7. Rearrange these coefficients into a column vector as shown in cells P3:P7. 8. Forecasting value can be computed by using the matrix multiplicator that invokes function MMULT as follows: =MMULT(C3:G49, P3:P7) where C3:G49 are the Predictors and Unit vector, P3:P7 are the regression model coefficients. You can enter this formula in the formula bar, having selected cells R3:R49. Press Ctrl+Shift+Enter to show the forecasting values in these cells. 9. You can evaluate the forecast error by using the Root Mean Square Error (introduced in the lecture) as follows: =SQRT(AVERAGE(POWER(R3:R48 - H4:H49, 2))) where R3:R48 - H4:H49 indicate the difference between target (R3:R48) and forecast (H4:H49) values. 10. Run these formulas to achieve the smallest forecast error (below 100). main job is to get the excel file done with the correct data which is only row 15. I have attached two excel files. The first is the data which needs to be used and the 2nd file is an example so don’t use it. USE DATA 15 which is on a separate file. Once done send back to me 1. Completed excel file.