Math 54 Small Project
Steps for How to Input Your Data into Excel and Run the Regression Analyses
Note: If you are instead using data from the first website of data list you can skip step 1 (the first step) since this step since for collecting the data will be straightforward. Only follow step 1 if you are using data from the second website from the data lists handout.
1. How to paste data obtained from online source into Excel in an organized fashion:
a) First, to input your data into an Excel spreadsheet, open up a new Excel file. Go to the web link for the project topic you desire, select and copy the data in the file.
b) After copying your selected data from a website on the internet, go into the Excel spreadsheet and click on the first cell, which is cell A1. Then use the command to paste your data into the spreadsheet.
c) Now you will realize that although the data looks like it is organized column by column how you would like it to appear, this is actually not the case. Everything is actually spaced out but only in the first column rather than multiple columns. To fix this, click on the Excel DATA tab at the top of the Excel screen, and then click on Text to Columns button (should be located near the middle of the screen.
d) Now your data is organized in columns like it should be so that you can select the data columns for the variables you are analyzing. Select and copy the data in each of the two columns of interest (each done separately), and then paste it into a new spreadsheet to have only the two columns of data in the new sheet that you wish to analyze. You can open a new spreadsheet by clicking the small button that is a plus sign inside of a circle, located at the bottom left of the screen.
e) Now your data is organized how it needs to be to perform. your regression analysis. Now for the steps on how to do the regression analysis.
2. You will need to install the regression Data Analysis toolbar onto your Excel version unless this toolbar already happens to exist. This can be easily done in just a few simple steps:
a) Click the File tab, and then click on Options.
b) Click Add-Ins, and then in the Manage box, select Excel Add-ins. Now click Go.
c) In the Add-Ins available box, select the Analysis ToolPak check box, and then click OK.
d) If you get prompted that the Analysis ToolPak is not currently installed on your computer, click Yes to install it.
e) After you load the Analysis ToolPak, the Data Analysis command is available in the Analysis group on the Data tab.
3. Carrying out the necessary least-squares regression analysis, creating the scatter and residual plots, and morel
a) First, select the DATA toolbar, and then click on the Data Analysis button.
b) Now a popup will come up with a list of options to select. Click on Regression and then click the OK button.
c) Now a dialog box will come up, asking what your inputs are for your X variable and for your Y variable. Select the range of cells in your spreadsheet corresponding to your X variable (explanatory variable) data and the range of cells in your spreadsheet corresponding to your Y variable (response variable) data. For example, if the X variable data in your spreadsheet are from cells A1 to A15, then type for the range "A1:A15", and if your Y variable data in your spreadsheet are from cells B1 to B15, then type for the range "B1:B15". Always use a colon ":" to tell Excel that it includes all cells in between the starting cell and ending cell.
Important note: Before finishing this part c) step you will also need to under the "Residuals" section of that dialog text box be sure to check mark the "Residual Plots" and "Line Fit Plots" criteria as this will be needed to have Excel generate your Scatter Plot and Residual Plots in the output.
d) Now click the OK button and you will now have your Excel Regression Summary Output to use for your written part of the project and to find your regression line. Note that the values under the "Coefficients" column on the bottom tablegive you the values of your bo and bi, according to the least-squares regression equation y= bix+ bo. Lastly, use the p-value column (for the X variable row) to determine whether or not there is a significant linear relationship between X and Y.
Note: Once all of your summary output tables and graphs are produced and provided, be sure to re-label the title and axes of your Scatter Plot and Residual Plot.