excel

Instructions

Problem Scenario: Michael Phelps is an American Olympic swimmer who has won more Olympic medals than any other Olympic athlete in the world. The spreadsheet contains information on all the medal races in which Michael Phelps competed over his entire Olympic career, attending 5 different summer games.* You will use Excel to do some calculations to determine the number of medals won, distance he swam, records set, and events he swam in. *Date Sources: https://www.olympics.org and https://www.olympiandatabase.com

Instructions: 1. Navigate to the sheet titled "Questions" to see the template to fill in. You will input your answers on this sheet. 2. In cells D32 and F32, sum up the columns to calculate: 1 In D32 -- The total distance (in meters) that Michael Phelps swam during all medal races in all Olympics. 1 In F32 -- The total time (in seconds) that Michael Phelps swam during all medal races in all Olympics. Format the cells so the numbers have commas, and no more than 2 decimal places 1. In cells H3 - H31, enter a formula which will calculate Michael Phelps' speed during each race in units of meters per second (m/s). Hints: 1 Write your formula for cell H3 first. Then use Excel's "autofill" feature by dragging the formula down to the remaining cells. 1 If you're not sure what the formula should be --- solve the problem on a piece of paper by hand first to guide you! 1 (Row 3) In Rio 2016, Michael Phelps swam in the 100m Butterfly and took 51.14 seconds to finish the race -- what was his speed? 1 (Row 4) In Rio 2016, Michael Phelps swam in the 200m Butterfly and took 113.36 seconds to finish the race -- what was his speed? 1 Now.... In which cells did you find the information to do your calculations for Row 3? What about for Row 4? Do you notice a pattern? 1 Finally, turn this into a formula to type into Excel. Format the cells so Excel only displays to 2 decimal places. 1. Use Excel to calculate some information on Michael Phelps' gold (1st place), silver (2nd place), and bronze (3rd place) medals: 1 In K4, K9, K14 -- Use Excel's COUNTIF function to determine the number of gold, silver, and bronze medals Michael Phelps won. Hints: 1 What range of cells should you search to determine how he ranked in each race? 1 When searching those cells, what number are you looking for to correspond to a gold, silver, or bronze medals? 1 Once you have typed a formula for bronze medals in K14, manually review the table and check your answer --- this should be easy. 1 In K5, K10, K15 -- Use Excel's SUMIF function to determine the distance Michael Phelps swam while earning his gold, silver, and bronze medals. Hints: 1 What range of cells should Excel search to determine how he ranked in each race? 1 When searching those cells, what number is Excel looking for to correspond to a gold, silver, or bronze medals? 1 What range of cells contains information on the distance of each race? These distances are what Excel should be summing up. 1 Once you have typed a formula for bronze medals into K15, manually review the table to check your answer. (In the 2004 Olympics in Athens, Michael Phelps swam the 200m Freestyle and the 100m Relay to earn bronze medals -- 300m total.) 2. Use Excel to calculate some information on the Olympic records (OR) and world records (WR) that Michael Phelps set while swimming in Olympic medal races. 1 In N4 and N9 -- Use Excel's COUNTIF function to determine the number of Olympic records and world records Michael Phelps set. 1 In N5 and N10 -- Use Excel's SUMIF function to determine the total distance Michael Phelps swam while setting the corresponding race records. 3. Use Excel to calculate some information on the types of events in which Michael Phelps competed. 1 In N14 - N17 -- Use Excel's COUNTIF function to determine the number of times Michael Phelps swam in a medal race for each of the four events (butterfly, freestyle, medley, and relay). CAUTION: You will not get credit if you manually type numbers into D32, F32, or columns H, K, N. You must have formulas. DONT' FORGET: Format cells D32, F32, and H3 - H31 as directed before handing in your assignment.

Questions