Download this file for the homework
assignment: ACTGPA.xls. To download this file in Internet
Explorer, right-click on the link, left-click on Save Target As ....,
left-click on Save, the file will download. When the download is completed,
left-click on Close.
Open the file 317HW2.xls in Microsoft Excel. To do this, start Excel, left-click on File > Open, locate and single left-click the file in the Open dialog box, and left-click Open.
Note that data headings are in Row 1. Data values are found in Rows 2-51.
Column A contains the subject number, Column B contains the subject's gender,
Column C contains the subject's composite ACT score, and Column D contains
their freshman year GPA.
Assignment
1. Compute the Mean and Standard Deviation values for the ACT and GPA data. Save these data in rows 53 & 54, respectively, in each data set's column. These values should be reported to one decimal place for ACT and 2 decimal places for GPA and in bold.
2. Compute the standard or z-scores for the GPA data using the z-score formula. Save these data in column F. Note that you should be using absolute cell reference in this calculation for the mean and standard deviation values & not just typing these values in the formula. Report these values to one decimal place.
3. Compute the standard or z-scores for the GPA data using the function STANDARDIZE. Save these data in column G. Report these values to one decimal place. Are F and G the same? Why or why not?
4. Use your answers in #2 to compute the percentile rank of each GPA score (use the function NORMSDIST). Save these values in column H. Report these values to one decimal place.
5. Use the NORMDIST (not NORMSDIST) function to compute the percentile rank for each ACT score. Save these data in Column I. Report these values to two decimal places.
6. Compute the average and standard deviation of the ACT scores for both males only and females only. Give these values to one decimal place and enter them in column J and K (make sure to label which is which).
7. Who is more variable in their GPA, males or females? How did you come to this conclusion? Provide quantitative evidence for your answer. Note that you should treat this data as a sample of a larger population.
8. What percent of all GPA scores are within the range of 2.5-3.5 (inclusive of both endpoints)? Explain how you computed your answer.
9. What percentage of all ACT scores are > 20? Explain how you computed your answer.
10. Print out your entire worksheet - make sure to label all values you compute.