Excel Post Exam

In Class

• The lead worksheet in the Excel workbook post-1.xls contains columns for recording the summary scores from two exams, a set of homeworks, a set of group projects and a final exam. The scores are recorded on auxillary worksheets in the workbook.
• Compute the summary scores on the auxillary worksheets and copy them to the appropirate columns on the lead worksheet.
• On the lead worksheet, for the exam i, exam ii, homework, groupwork and final columns compute the class average.
• On the lead worksheet, for each student compute the weighted total and the percent. The column weights are supplied on the worksheet.
• On the lead worksheet, for each student calculate a final letter grade based on the grading scale given on the worksheet.
• Save the worksheet in your Euclid account as: post-1-yourlastname.xls
Take Home

1. U.S. Census Data -- State Population Projections
• Use the Excel workbook post-2.xls to provide a summary report about the raw data recorded in the Census Data file (referenced in Excel Exam Source Files).
• The raw data provides population estimates for various strata in the U.S. population:
1. White (Total) and White Female
2. Black (Total) and Black Female
3. American Indian, Eskimo, Aleut (Total) and American Indian,Eskimo, Aleut Female
4. Asian and Pacific Islander (Total) and Asian and Pacific Islander Female
5. Hispanic (Total) and Hispanic Female
6. Non-Hispanic White (Total) and Non-Hispanic White Female
for each of the 50 states and the District of Columbia. By calculation the male estimates for each of the above groups can also be computed.
• On the worksheets W 1995 vs 2005, B 1995 vs 2005 and H 1995 vs 2005 group the 50 states and the District of Columbia into the four (4) major geographic regions used for partitioning the U.S.: The NorthEast, The South, The MidWest, The West. That partition is explicitly described in the Recreation Activities file (referenced in Excel Exam Source Files).
• On the worksheet W 1995 vs 2005 construct a summary chart/graph to compare for White Women the 1995 population estimates to the 2005 population estimates for the four major geographic regions.
• On the worksheet B 1995 vs 2005 construct a summary chart/graph to compare for Black Men the 1995 population estimates to the 2005 population estimates for the four major geographic regions.
• On the worksheet H 1995 vs 2005 construct a summary chart/graph to compare for Hispanics the 1995 population estimates to the 2005 population estimates for the four major geographic regions.
• On the worksheet Distribution 1995 vs 2005,construct a pie chart for the 1995 population for the distribution of the 6 strata (White; Black; American Indian, Eskimo, Aleut; Asian and Pacific Islander; Hispanic; Non-Hispanic White). On the worksheet Distribution 1995 vs 2005,construct a pie chart for the 2005 population for the distribution of the 6 strata (White; Black; American Indian, Eskimo, Aleut; Asian and Pacific Islander; Hispanic; Non-Hispanic White).
• Each chart/graph should be appropriately titled to describe its contents. The axes should have appropriate labels to describe their variables. Legends should be set up to distinguish the plotted variables.
• E-mail the completed workbook to me with the file name: post-2-yourlastname.xls

2. Other Data Sets
1. Weight Gain in Rats Exposed to Aconiazide (referenced in Excel Exam Source Files)
• Use the worksheet Weight Gain for Rats in the workbook post-3.xls
• Construct a scatter plot to graphically illustrate the relationship between dosage of Aconiazide and measured weight gain - if any relationship exists.
• Compute the correlation coefficient between dosage of Aconiazide and measured weight gain.
• Provide a statement/paragraph to interpret the information obtained in the above two steps.
2. Educational Spending (referenced in Excel Exam Source Files)
• Use the worksheet Educational Pay in the workbook post-3.xls
• Construct a scatter plot to graphically illustrate the relationship between average salary paid to teachers and average amount spent on students.
• Sort the data by average salary paid to teachers (decending order). Where does Texas lie? Where does New Mexico lie? Where does Oklahoma lie? Where does California lie?
• Sort the data by average amount spent on students (descending order). Where does Texas lie? Where does New Mexico lie? Where does Oklahoma lie? Where does California lie?
• Suppose that Texas could shift its position in the rankings of average amount spent on students to the 75 percentile. If there is a linear relationship between average salary paid to teachers and average amount spent on students, what would be the average salary paid to teachers under such a shift?
3. Passenger Car Mileage (referenced in Excel Exam Source Files)
• Use the worksheet Passenger Car Mileage in the workbook post-3.xls
• Construct a scatter plot for the variables Horse Power vs Mileage
• Construct a scatter plot for the variables Horse Power vs Speed
• Provide a statement/paragraph to interpret the information obtained in the above two steps.
• For each of the values of Horse Power 50, 100, 150, 200, 250, 300, 350, 400 predict values of mileage. Are the predictions reasonable? Why or why not.
• For each of the values of Horse Power 50, 100, 150, 200, 250, 300, 350, 400 predict values of speed. Are the predictions reasonable? Why or why not.
• An alternative Excel (spreadsheet) function to FORECAST, which uses a linear model (best fit straight line) to predict values is GROWTH, which uses an exponential model (best fit exponential curve) to predict values. (See Help for information about the parameters which GROWTH takes.) For each of the values of Horse Power 50, 100, 150, 200, 250, 300, 350, 400 use GROWTH to predict values of mileage. Are the predictions better or worse than those obtained using FORECAST two steps above?
4. Each chart/graph should be appropriately titled to describe its contents. The axes should have appropriate labels to describe their variables. Legends should be set up to distinguish the plotted variables.
5. E-mail the completed workbook to me with the file name: post-3-yourlastname.xls