open the Excel Spreadsheet (link below). Finally, open the document called Exercise (linked below and also in Course Resources) and answer the questions.Important Note: This should be done using a PC

NAME_____________________________

Statistics with Excel

1. Load the file called HUB670 Statistics Exercise Data.xls (located in Week 4 of the Blackboard eCompanion) into Excel.

2. Install the Analysis ToolPak (See HUB670 Class 8 Excel Statistics PowerPoint presentation)

3. Produce the descriptive statistics for the Age variable in the Exercise Data spreadsheet. Go to the Data tab and select Data Analysis/Descriptive Statistics. Put the cursor in the Input Range box and highlight the Age column. Check the Labels in First Row box. Check the Summary Statistics box then click OK. Report the following from the new sheet.
   What is the average (mean) Age?____________
   What is the standard deviation of Age?_________
   What is the Age range?_______

4. What is the minimum _____ and maximum_____ Age?

5. Run the Independent t-test to compare the ages of males and females. Follow these steps from the PowerPoint slides: (a) create two new columns next to each other, (b) put the Age data for the Males (M) in the first column, (c) put the Age data for the Females (F) in the second column, (d) select the Data tab and select Data Analysis, (e) select the t-test (two sample assuming equal variance), (f) put the Age data for the Males in Variable 1 Range and the Age data for the Females in Variable 2 Range, (f) check the Labels in First Row box, and (g) click OK and report the output from the new sheet.
   What is the mean Age for the Males? ____________
   What is the mean Age for the Females? ___________
   What is the value of the t statistic? ______________
   What is the two-tailed significance level of the t statistic? ______
   Is there a significant difference between the Males and Females (Yes/No)?
   Which group has the higher Age (Males or Females)? _______________

6. Do a regression analysis between Workload and Stress. Return to the Exercise Data spreadsheet. Go to the Data tab and select Data Analysis/Regression. Highlight only the cells with numbers (do not select the label) for Mean Stress (1-5) and put the data in your Input Y Range. Next, highlight the number cells for Mean workload (1-5) and put the data in your Input X Range. Next, uncheck the Labels box if it is checked (the regression sometimes will not run if this box is checked). Click OK. Look at the new sheet and report the following.
   What is the value for the Multiple R? _____________
   What is the significance level for the Regression under ANOVA (the value may appears in scientific notation, so select the cell and format it as a number with six decimal places) _______
   Is the Regression (The Multiple R) statistically significant? (Yes/No)
   What is the Intercept Coefficient? __________
   What is the X Variable Coefficient (the slope)? __________

7. Make a scatterplot of the relationship between Mean Workload (1-5) and Mean Stress (1-5). Highlight the Workload and Stress columns. Go to the Insert tab and select Scatter. Pick the first scatter plot choice (upper left hand option). Pick the first Chart Layout and label your axes (X = workload, Y = Stress). Copy and paste the scatterplot at the end of this electronic document.

Is this a positive or negative relationship? ___________________

What is the correlation between Workload and Stress (report the Multiple R again)? ________

1. Draw the Line of Best fit for your scatterplot
   Pick two extreme values of X (Workload) and put each in the regression equation below to find the two Y (Stress) values. On the scatterplot you created from Item 7, connect the two coordinates with a straight line using the regression equation.
   

Y’ = a + (b * X)
Stress = Intercept + (Slope * Workload), where
Intercept = Intercept Coefficient from Item 7
Slope = X Variable Coefficient from Item 7
------------------------------------------------------------------------------------------------

Example:
Small Value of X: If Workload (X) = 0, Intercept = .5, and Slope = 1, then
Stress = .5 + (1 * 0)
Stress = .5
Large Value of X: If Workload (X) = 5, Intercept = .5, and Slope =1, then
Stress = .5 + (1 * 5)

Stress = 5.5

Draw a line between the coordinates:
Workload (0) and Stress (.5)
Workload (5) and Stress (5.5)