The file P03_18.xlsx contains the times in the Chicago marathon for the top runners each year (the top 10,000 in 2006 and the top 20,000 in 2007 and 2008).
a. Merge the data in these three sheets into a single sheet named 2006–2008, and in the new sheet, create a variable Year that lists the year.
b. The Time variable, shown as something like 2:16:12, is really stored as a time, the fraction of day starting from midnight. So 2:16:12, for example, which means 2 hours, 16 minutes, and 12 seconds, is stored as 0.0946, meaning that 2:16:12 AM is really 9.46% of the way from midnight to the next midnight. This isn’t very useful. Do whatever it takes to recode the times into a new Minutes variable with two decimals, so that
2:16:12 becomes 136.20 minutes. (Hint: Look up Time functions in Excel’s online help.)
c. Create a new variable Nationality to recode Country as “KEN, ETH,” “USA,” or “Other,” depending on whether the runner is from Kenya/Ethiopia (the usual winners), the USA, or some other country.
d. Use StatTools to find the mean, median, standard deviation, and first and third quartiles of Minutes, broken down by Nationality. Also, create side-byside box plots of Minutes, again broken down by Nationality. Comment on the results.
e. Repeat part d, replacing Nationality by Gender.