Input Y Range is where the response variable (Sales in our case) is located.If you’re using the CSV or XSLX file, you should mirror these options. Now that we can select different built-in analyses, we’ll launch the regression tool. You’ll then select the Analysis Toolpak and it should now be visible in the Data tab.Select the Add-ins section and go to Manage Excel Add-ins.Go to the Data tab, right-click and select Customize the Ribbon.
If you don’t have the Toolpak (seen in the Data tab under the Analysis section), you may need to add the tool. Y = 1,383.471380 + 10.62219546 * X Doing Simple and Multiple Regression with Excel’s Data Analysis ToolsĮxcel makes it very easy to do linear regression using the Data Analytis Toolpak. We now have our simple linear regression equation. The intercept is the “extra” that the model needs to make up for the average case. To calculate our regression coefficient we divide the covariance of X and Y (SSxy) by the variance in X (SSxx) Sheets("Regression Output").Range("A:Y").The sum fields are our SSxx and SSxy (respectively). Sheets("Regression Output").Range("A:Y").ClearContents Of course, each time you could create a temporary new sheet to accommodate the new filtered table, put the regression data where you like on the Regression Output sheet, then delete that new sheet. Please check out the comments in the code too. The results go to cell R3 of the Regression Output sheet. Set XValsRange = Intersect(XValsRange, XValsRange.Offset(1)) 'exclude the headersĪpplication.Run "ATPVBAEN.XLAM!Regress", YValsRange, XValsRange, False, False,, Sheets("Regression Output").Range("$R$1"), False, False, False, False,, False Set XValsRange = Intersect(NewTableRange, Sheets("Regression Output").Range("C:M")) 'find x values range Set YValsRange = Intersect(YValsRange, YValsRange.Offset(1)) 'exclude the headers Set YValsRange = Intersect(NewTableRange, Sheets("Regression Output").Range("O:O")) 'find y values range Set NewTableRange = Sheets("Regression Output").Range("A1").CurrentRegion 'find the extent of the new filtered data: Sheets("Regression Data").Range("A1:O" & LR1).AdvancedFilter Action:=xlFilterCopy, _ĬriteriaRange:=Sheets("Input").Range("D3:D4"), _ĬopyToRange:=Sheets("Regression Output").Range("A1"), Unique:=False 'Create a new table of filtered data note that cell D3 of the Input sheet will need to be the same as the header in cell A1 of the Regression Data sheet. '(note that I only use LR1 becuse if your LR2 is different from LR1 I suspect there will be a problem with the regression tool) LR1 = Sheets("Regression Data").Range("C" & Rows.Count).End(xlUp).Row ' determine extent of source data
0 kommentar(er)
