Upon using a spreadsheet to create a graph of raw data it is frequently desirable to generate the best fit equation for that data. Most spreadsheets have the capability of performing a polynomial regression analysis of any order. By means of an example, the following steps can be followed to obtain the equation of best fit for a set of data. In an attempt to keep this discussion general, a discipline specific example will not be presented. Also, nonlinear data will be used, although linear data can also be analyzed.
1. Gather the data. For the example, the following data will be used.
|Y Data||X Data|
2. Enter the data into a spreadsheet in two columns with the Y data to the left of the X data. Since the data is obviously nonlinear, create a third column that is the X data raised to the second power (X2) as shown below.
|Y Data||X Data||X2|
3. Select the regression analysis option within the spreadsheet you are using. Highlight the column of Y data as the dependent variable and the X and X2 data as the independent variables. Selecting only the X data as the independent variable will yield a linear equation.
4. The menu should have an OUTPUT selection option. Select OUTPUT and then place the cursor somewhere off of your raw data. It is necessary to place the output off your raw data so that your data is not erased when the analysis is performed.
5. Select GO from the regression analysis menu. Your output should look like the following.
|Y Data||X Data||X2||X3||X4|
8. In the regression analysis option, highlight the X, X2, X3 and X4 data as the independent data. Rerun the regression analysis and the output will look like the following.
Regression Output: Constant 0.00873 Std Err of Y Est 0.138587 R Squared 0.999903 No. of Observations 6 Degrees of Freedom 1 X Coefficient(s) 0.53201 1.098611 0.50648 0.0875 Std Err of Coef. 0.510028 0.487215 0.153745 0.015278
9. The new equation correlating the data is as follows,
- Y = 0.00873 - 0.53201*X + 1.098611*X2 - 0.50648*X3 + 0.0875*X4. (2)
The new R Squared value is 0.999903, which indicates a very good fit for the data.
10. The equation can now be programmed inside the spreadsheet and, if desired, used to calculate values for Y so that a comparison between the raw data and calculated data can be made. Also, it would now be possible to generate a graph with a smooth curve through the raw data.