The row labeled Intercept will always be the constant in the polynomial that is not multiplied by x. Each of the rows labeled X Variable, except X Variable 1, correspond to one of the constants in the polynomial. 3 (note that the columns in this spreadsheet have been modified to clearly show their contents). If you have selected to have the results of the fit put in a new worksheet ply, then you will see something like what is shown in Fig. Once the x and y ranges are set, you can set any other parameters as desired, and click OK. 1 this would be columns B through E (cells B2 to E28). While entering the y values is no different than for a linear regression, inputting the x values is very different in that the Input X Range box must contain all of the columns containing a power of x. 1 this would be the cells F2 to F28 (enter F2:F28 in the Input Y Rangebox). In the Input Y Range box type in the cell addresses that contains your y values (or click and drag to select them from the worksheet).
Sample spreadsheet that is ready to be fit to the cubic expression y = ax + bx 2+ cx 3 + d using Excel’s regression package. Once the spreadsheet is set up as shown below, select Tools, Data Analysis from the menu bar and scroll down to Regression, select it and click OK.įigure 1. Note that in addition to x 1, x 2 and x 3 there must be a column containing x 0, which contains only ones. e., y = ax + bx 2+ cx 3 + d, where a, b, c and d are constants that we need to find), then we would create columns containing the independent variable to the desired powers, as shown in Fig. For example, if we wanted to fit a set of data to a third order polynomial (i. One simple trick is to create columns each containing the variable of interest to the requisite power. If you added headers in the first cells of each column, click the check box next to "Labels."Ĭlick the radio button next to "Output Range" then place your cursor into the text box next to the radio button and click on cell C1.Ĭlick "OK" and your regression table will appear on the spreadsheet starting in cell C1.It is possible to have Excel perform a non-linear least square regression. Place your cursor into the "Input X Range" text box and repeat the process to select the data set that holds your independent, or explanatory, variables. Now click and hold on the top cell in the column that contains your dependent variables, drag the mouse down to the last data-holding cell in that column and release the mouse button. Place your cursor into the "Input Y Range" text box. Select "Regression" from the list that appears in the Data Analysis window and then click "OK." If desired, you can add headers to your data in cells A1 and B1 so that you can tell the data sets apart.Ĭlick on the "Data" tab at the top of the Excel window and then click the "Data Analysis" button when it appears on the ribbon. Repeat the process with your second set of values down the second column. Continue to enter the rest of the set of values down the cells in the first column. Select cell A1 and enter the first value for your first set of values. Click the small check box next to "Analysis ToolPak" and click "OK." You will now have access to the regression feature. Click on "Add-Ins" on the left side of the Options window and then click "Go" when the button appears at the bottom of the Options window.
0 kommentar(er)
