computer project Statistics

you have attached the project instructions and alos the XL file that you will need to do the project.
please let me know if you have any questions
thank you

DO THIS FIRST!

To complete this project, you will need to use the Analysis ToolPak in Excel; however, first you will need to load it. Follow these steps to load the Analysis ToolPak in Excel:

• Open Excel on the computer you will be using to complete this project.
• Click the File tab at the top left.
• Click Options toward the bottom left.
• Click Add-Ins on the left.
• Click Go toward the bottom.
• In the Add-Ins available box, select the Analysis ToolPak check box and the Analysis ToolPak – VBA check box, and then click OK.
• You are now ready to complete this project.

 

__________________________________________________________________________________________________

 

You are going to complete a project involving estimates based upon data gathered in the 2010 Census. For each county in Kentucky, you are going to have the median household income and the percentage of high school graduates in the county amongst adults (age 25+).

 

(10 points)  Using Excel to make a scatterplot:

• Go to our course on Blackboard.
• Click on the Computer Projects tab on the left side of the screen.
• Click on Computer Project #1.
• Open the Excel file I’ve posted. (You should see three columns of information: County, Percent High School Graduates, and Median Household Income.)
• Put your name in cell E1. –Andrei
• Be sure to save your work early and often!
• Select cells B1:B121 and cells C1:C121.
• With those cells selected, click on the Insert tab.
• In the Charts section, click on the picture that looks like a scatterplot (likely called Insert Scatter (X,Y) or Bubble Chart).
• Choose the scatterplot in the upper left-hand corner under Scatter.
• Toward the upper left-hand corner of your screen, click the drop-down arrow beside “Quick Layout.” Select Layout 1; it gives the scatterplot axis labels and a title.
• Replace the title “Median Household Income” at the top of the graph with the title “Median Household Income Versus Percent High School Graduates in KY Counties.”
• Click on the box on the right that contains the legend that says “Median Household Income” and press your keyboard’s delete button.
• Replace “Axis Title” on the horizontal axis with “Percent High School Graduates.”
• Replace “Axis Title” on the vertical axis with “Median Household Income ($).”
• Click on one of the horizontal gridlines in the background. Some circles should appear at the ends of each line. Press your keyboard’s delete button to delete the gridlines.
• Click on the Format tab at the top of the screen.
• In the Current Selection section in the upper left-hand corner of the screen, click on the arrow in the drop-down menu and select Horizontal (Value) Axis.
• Still in the Current Selection section, click on Format Selection (just below the box that now says “Horizontal (Value) Axis”). A box that says “Format Axis” should appear on the right side of your screen. Beside “Minimum,” type 50 in the box and press your keyboard’s enter button.
• Drag the scatterplot until it is near the top of the spreadsheet, just below your name.
• Click in cell E20 (or another cell that is just below the scatterplot). Based on the scatterplot, type a description of the association between Median Household Income and Percent High School Graduates.

 

 

(20 points)  Using Excel to find a regression equation:

• Click on the Data tab.
• In the Analysis section in the upper right-hand corner, click on Data Analysis.
• Choose Regression and click on OK.
• For Input Y Range, type C2:C121.
• For Input X Range, type B2:B121.
• Click on OK. The regression results will appear on a different sheet (such as Sheet 4 or Sheet 5) whose tab is to the left of the Sheet 1 tab.
• In the bottom table, the slope of the regression equation is the number beside “X Variable” and under “Coefficient.” In the same table, the y-intercept of the regression equation is the number beside “Intercept” and under “Coefficient.”
• In the sheet where these results appeared, click in cell K2. Type the regression equation in the form y=a+bx. (Round the y-intercept and the slope to the nearest tenth.)
• Click in cell K4. Type an interpretation of the slope of the regression equation in the context of this problem.
• Click in cell K6. Use the regression equation to predict the median household income for a Kentucky county with 75% of its adults having at least a high school education. (Show your work by typing in cell K6.)
• Be sure to save your work one last time before you quit!