Excel Homework 5
(70 pts possible)
Module/Week 6’s assignment has two parts. The objective of your fifth Excel assignment is to learn to construct confidence intervals and conduct hypothesis tests in Excel based on data whose population parameters are known. You will also answer questions about your results. Part One involves constructing a confidence interval for the population mean based on sample data; Part Two involves testing a hypothesis about the same sample in comparison to the known population. First, be sure you view the presentation found in the Reading & Study folder in Module/Week 6. This presentation provides information and goes through the steps you will need to be familiar with in order to complete this assignment.
Research Question for Parts 1 and 2:A health psychologist wants to study overall student health on a university campus. One measure he decides to take is that of minutes exercised per week. Assume that we know from previous studies that the population mean for minutes of exercise per week for college students is μ = 100 with a standard deviation of σ = 25.
The health psychologist in question is particularly interested in the myth of the “Freshmen 15.” This myth claims that most freshmen gain weight during the first year of college, mostly due to bad eating habits and lack of exercise and sleep. He wonders if the Freshmen on his campus actually exercise less than the general population of college students. He randomly selects a sample of 50 Freshmen students and asks them how many minutes they exercise per week. The raw data collected by the health psychologist are in this module/week’s Data Set, entitled “Data Set 5”.
Part 1: Confidence Interval
1.
Open the Excel file entitled “Data Set 5”. The file contains the following: a) raw data for the sample of 50 Freshmen; b) a results table for Part 1; c) Questions for Part 1; d) a results table for Part 2; and e) Questions for Part 2.
2.
Construct a 95% Confidence Interval of the population mean of minutes exercised per week.
a. First, fill in cells with information given in the research question above (N and sigma). (4 pts)
b. In the appropriate cell in the table, compute the sample mean using the raw data given in column A. (Use the AVERAGE function.) (4 pts)
c. Determine the alpha level for this problem and type it in the appropriate cell. (4 pts)
d. As seen in this module/week’s presentation, use the CONFIDENCE function to compute the 95% confidence interval in the appropriate cell in the table. (4 pts)
e. Again as seen in the presentation, compute the lower and upper limits of the confidence interval in the appropriate cells.(4 pts)
3.
Answer all five questions beneath the first table. Type answers directly into the Excel file as indicated. (Questions = 3 points each for total of 15 pts.)
Part 2: Hypothesis Test
1. State the null and alternative (research) hypothesis in symbolic form. The hypothesis should be written based on the following information from the research situation: “The health psychologist in question is particularly interested in the myth of the ‘Freshmen 15.’ This myth claims that most freshmen gain weight during the first year of college, mostly due to bad eating habits and lack of exercise and sleep. He wonders if the Freshmen on his campus actually exercise less than the general population of college students (which has a μ = 100 and σ = 25).” (Remember that your hypothesis should include evaluators such as =, <, > or a combination of these.) (2 pts)
2. Is your hypothesis directional or non-directional? Read the wording again in question 1 if you are unsure. The evaluators (=, <, >, etc.) you used in stating the hypotheses in question 1. above should also give you a clue. Fill in the cell with either “one-tailed” or “two-tailed” based on whether the alternative hypothesis is directional or not. Also, if you fill in “one-tailed”, answer the question to the right of the table concerning the direction of the tail. This decision will help you determine the critical values of your test statistics later, so think carefully! (2 pts)
3. Fill in the cells for N, μ, and σ which are already known. These will be used in formulas as shown in this week’s presentation. (2 pts)
4. In the appropriate cell in the table, compute the Standard Error of the Mean (σM) using the steps shown in this week’s presentation. (2 pts)
5. Fill in the sample mean (M), again using the AVERAGE function. (2 pts)
6. We are going to test our hypothesis at the .05 level of significance. Enter the alpha value in the appropriate cell. (2 pts)
7. Find the critical Z value for our test, based on the alpha level, using the NORMSINV function as shown in this module/week’s presentation. Remember to consider the direction(s) of your hypothesis when computing this Z value! (2 pts)
8. Compute the sample Z score using the steps gone over in this module/week’s presentation. (2 pts)
9. Fill in the critical p-value based on your alpha level. (2 pts)
10. Compute your sample’s p-value (based on your sample’s Z score) by using the NORMSDIST function as shown in this week’s presentation. (2 pts)
11. Answer all five questions underneath the second table directly in Data Set 5, after each question as indicated. (Questions = 3 points each for total of 15 pts.)
Save your work as “yourname_excel5.xls” and submit it by
11:59 p.m
| Number of Minutes Exercised Per Week | PART ONE | Sample N | Sample Mean | Sigma (σ) | Alpha | 9 | 5% | Lower Limit | Upper Limit | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 89 | Freshmen | 50 | 89.14 | 35.68 | 0.05 | 1. | 96 | 19.21 | 159.07 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 43 | 25 (-2) | No formula utilized for CI (-4) | E3-H3, E3+H3 (-2) | 12.4.12 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 47 | 1. Conclusion in words (fill in the blanks): | There is a 95 % chance that the range 19.21 | to | 100 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 143 | Write the words/numbers that go in the blanks here: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 115 | 2. What is the probability (or chance or risk) that the statement that “the population parameter falls between these limits” is not correct? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 136 | Answer: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 29 | 3. Does the confidence interval based on this sample contain the true population mean? (we don’t always know this, but in this case we do) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 44 | yes, the population mean is given as 100 and is covered in this range of 19.21 to 159.07 | No (-3) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 129 | 4. What might the answer to number 3 tell us about our sample of Freshmen college students? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 98 | This suggests that the sample is a representative of the population | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 99 | 5. If you were to construct a 99% CI, would you expect it to be wider or narrower than the 95% CI you just figured? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| For 99%, critical value of z is 2.58 | the range is | -2.92 | 181.20 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 64 | yes, the range is wider than the 95% CI shown above. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 90 | PART TWO | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 132 | Null Hypothesis (H0) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 72 | Research Hypothesis (H1) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 141 | One- or Two-tailed? | If one-tailed, which direction, or tail ( | left | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 26 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 144 | Sample Size (N) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 78 | Population Mean (μ) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 86 | Population Standard Dev. (σ) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 110 | Stand. Error of Mean (σM) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 69 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 37 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 42 | Alpha | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 77 | Critical Z Value (cut-off score) | Be sure your value is in the correct direction(s)! | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 120 | Sample Z | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 51 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 56 | Critical p-value | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 87 | Sample p-value | Part 2 not comlete (-18) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 114 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 123 | 1. Are the results of your hypothesis test statistically significant? What does this mean? | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 28 | Answer: the hypothesis is not statistically significance since the p value 0.9843 is less than the significance level 5 | Significant (-3) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 48 | 2. Decision about null and research hypotheses (write in sentence form): | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 107 | Answer: the null and research hypotheses is rejected due to the low p value | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 3. If the population mean were unknown, what would be the best estimate of this value? | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| Answer: a population mean of 80.14 will be appropriate if the significant value is 1 and none if its 5 | Sample mean (-3) | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 105 | 4. P-values represent probabilities based on the under the normal curve, and they range from to . | Area, 0 to 1 (-2) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 135 | P- values represent probabilities based on the significance value under the normal curve and they range from 0.05 to 1.0 | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 103 | 5. The particular type of hypothesis test we used here is called the test. We can use this test when the values are known. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 57 | the particular type of hypothesis test used here is called one tailed test. We can use this test when the significance values are known. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| z, population (-3) | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 142 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 45 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 133 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| 130 | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
PSYC 354
Excel Homework 5
(70 pts possible)
Module/Week 6’s assignment has two parts. The objective of your fifth Excel assignment is to learn to construct confidence intervals and conduct hypothesis tests in Excel based on data whose population parameters are known. You will also answer questions about your results. Part One involves constructing a confidence interval for the population mean based on sample data; Part Two involves testing a hypothesis about the same sample in comparison to the known population. First, be sure you view the presentation found in the Reading & Study folder in Module/Week 6. This presentation provides information and goes through the steps you will need to be familiar with in order to complete this assignment.
Research Question for Parts 1 and 2: A health psychologist wants to study overall student health on a university campus. One measure he decides to take is that of minutes exercised per week. Assume that we know from previous studies that the population mean for minutes of exercise per week for college students is μ = 100 with a standard deviation of σ = 25.
The health psychologist in question is particularly interested in the myth of the “Freshmen 15.” This myth claims that most freshmen gain weight during the first year of college, mostly due to bad eating habits and lack of exercise and sleep. He wonders if the Freshmen on his campus actually exercise
less
than the general population of college students. He randomly selects a sample of 50 Freshmen students and asks them how many minutes they exercise per week. The raw data collected by the health psychologist are in this module/week’s Data Set, entitled “Data Set 5”.
Part 1: Confidence Interval
1. Open the Excel file entitled “Data Set 5”. The file contains the following: a) raw data for the sample of 50 Freshmen; b) a results table for Part 1; c) Questions for Part 1; d) a results table for Part 2; and e) Questions for Part 2.
2. Construct a 95% Confidence Interval of the population mean of minutes exercised per week.
a. First, fill in cells with information given in the research question above (N and sigma). (4 pts)
b. In the appropriate cell in the table, compute the sample mean using the raw data given in column A. (Use the AVERAGE function.) (4 pts)
c. Determine the alpha level for this problem and type it in the appropriate cell. (4 pts)
d. As seen in this module/week’s presentation, use the CONFIDENCE function to compute the 95% confidence interval in the appropriate cell in the table. (4 pts)
e. Again as seen in the presentation, compute the lower and upper limits of the confidence interval in the appropriate cells.(4 pts)
3. Answer all five questions beneath the first table. Type answers directly into the Excel file as indicated. (Questions = 3 points each for total of 15 pts.)
Part 2: Hypothesis Test
1. State the null and alternative (research) hypothesis in symbolic form. The hypothesis should be written based on the following information from the research situation: “The health psychologist in question is particularly interested in the myth of the ‘Freshmen 15.’ This myth claims that most freshmen gain weight during the first year of college, mostly due to bad eating habits and lack of exercise and sleep. He wonders if the Freshmen on his campus actually exercise
less
than the general population of college students (which has a μ = 100 and σ = 25).” (Remember that your hypothesis should include evaluators such as =, <, > or a combination of these.) (2 pts)
2. Is your hypothesis directional or non-directional? Read the wording again in question 1 if you are unsure. The evaluators (=, <, >, etc.) you used in stating the hypotheses in question 1. above should also give you a clue. Fill in the cell with either “one-tailed” or “two-tailed” based on whether the alternative hypothesis is directional or not. Also, if you fill in “one-tailed”, answer the question to the right of the table concerning the direction of the tail. This decision will help you determine the critical values of your test statistics later, so think carefully! (2 pts)
3. Fill in the cells for N, μ, and σ which are already known. These will be used in formulas as shown in this week’s presentation. (2 pts)
4. In the appropriate cell in the table, compute the Standard Error of the Mean (σM) using the steps shown in this week’s presentation. (2 pts)
5. Fill in the sample mean (M), again using the AVERAGE function. (2 pts)
6. We are going to test our hypothesis at the .05 level of significance. Enter the alpha value in the appropriate cell. (2 pts)
7. Find the critical Z value for our test, based on the alpha level, using the NORMSINV function as shown in this module/week’s presentation. Remember to consider the direction(s) of your hypothesis when computing this Z value! (2 pts)
8. Compute the sample Z score using the steps gone over in this module/week’s presentation. (2 pts)
9. Fill in the critical p-value based on your alpha level. (2 pts)
10. Compute your sample’s p-value (based on your sample’s Z score) by using the NORMSDIST function as shown in this week’s presentation. (2 pts)
11. Answer all five questions underneath the second table directly in Data Set 5, after each question as indicated. (Questions = 3 points each for total of 15 pts.)
Save your work as “yourname_excel5.xls” and submit it by 11:59 p.m
Page 1 of 2
