As a weather analyst, you have been tracking daily high and low temperatures for Oklahoma City,…

As a weather analyst, you have been tracking daily high and low temperatures for Oklahoma City, Tulsa, and Lawton during June, July, and August. Each month’s data are stored in their own workbooks with each city’s data stored in its own worksheet. You need to apply consistent formatting and enter formulas for all worksheets. On each workbook, you need to create the summary worksheet to identify the record high and low temperatures by day and identify the respective cities. Finally, you will link the data to a master workbook.

a. Open e08m2june, and then save it as e08m2june_LastnameFirstname.

b. Group the city worksheets, and then do the following:

• Fill the formatting of cells A1, A2, and A5:C5 from the OKC worksheet to the other city worksheets. • Enter dates 6/1 to 6/30 in this date format (no year) in the Date column. Apply Orange, Accent 6, Lighter 80% fill to the dates.

• Split the window after 6/10. Scroll down in the second window to see the Monthly Records section. Note that only the active window in the grouped worksheets is split.

• Enter a function in cell B39 to calculate the highest temperature of the month. Enter a function in cell C39 to calculate the lowest temperature of the month.

• Use a nested MATCH function within the INDEX function in cell B40 to identify the date for the highest temperature. The dataset may contain several identical highest temperatures, but the nested function will identify the first date containing the match.

• Use a nested MATCH function within the INDEX function in cell C40 to identify the date for the lowest temperature.

c. Use a Web browser to go to www.wunderground.com, a weather Web site. Locate and click the History Data link below the Local Weather menu, and then do a search for OKC. Copy the URL, and then create a hyperlink to this Web page for cell A3 in the OKC worksheet. Add a ScreenTip stating Click to see weather history for Oklahoma City.

d. Adapt step c to create hyperlinks for the Tulsa and Lawton worksheets as well. Use the Lawton-Fort Sill Airport (LAW) location for the weather link. Check each hyperlink to ensure it works correctly.

e. Enter the following 3-D formulas in the appropriate cell in the Summary worksheet:

• Calculate the highest temperature from the three cities for 6/1. Copy the formula down the High column.

• Calculate the lowest temperature from the three cities for 6/1. Copy the formula down the Low column.

• Enter a nested IF function in cell C6 to determine which city had the highest temperature. Remember to enclose city names in double quotation marks. Use Help if needed to help you understand a nested IF statement. Copy the formula down the High-City column.

• Enter a nested IF function in cell E6 to determine which city had the lowest temperature. Copy the formula down the Low-City column.

f. Enter formulas in the shaded Monthly Records section (below the daily data) to identify the highest and lowest temperatures. Enter nested INDEX and MATCH functions to identify the dates and cities for the respective highest and lowest temperatures.

g. Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side of each worksheet. Select the option to center the worksheet data horizontally on each sheet. Save the workbook.

h. Open e08m2july, and then save it as e08m2july_LastnameFirstname. Open e08m2august, and then save it as e08m2august_LastnameFirstname. Study the workbooks, and then adapt steps b and e above as necessary to enter formulas on grouped city worksheets and to enter 3-D formulas on the Summary worksheets for these two workbooks. The formatting and hyperlinks are done for you. Add your name on the left side of the footer for each worksheet.

i. Split the Summary sheet worksheets to show the first eight days in the top windows and the past four or five days and the summary data in the bottom window. Arrange the open workbook windows vertically with June on the left side, July in the middle, and August on the right side. Save this arrangement as a workspace file named e08m2workspace_LastnameFirstname.

j. Open e08m2summer, and then save it as e08m2summer_LastnameFirstname. Insert external reference links to the respective cells on the monthly Summary worksheets. Format dates and labels appropriately. Enter your name on the left side of the footer. Save the workbook.

k. Save and close the workbooks, and submit based on your instructor’s directions.