EXP ECH09 H3 Weather Analyst 1.1 complete solutions correct answers key

EXP ECH09 H3 Weather Analyst 1.1 complete solutions correct answers key

Project Description:

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

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Open the downloaded workbook named exploring_e09_grader_h3_June.xlsx and save it as exploring_e09_grader_h3_June_LastFirst.
 

0

2

Group the city worksheets. Fill the formatting of cells A1, A2, and A5:C5 from the OKC worksheet to the other city worksheets.

 

6

3

Enter dates 6/1/2015 to 6/30/2015 and format them as 6/1 to 6/30 (no year displayed) in the Date column. Apply Orange, Accent 6, Lighter 60% fill to the dates.

12

4

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.
 

9

5

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.
 

8

6

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

 

8

7

Use a Web browser to go to www.wunderground.com, a weather Web site.  At the top of the page, click More, click Historical Weather and submit a search for OKC, Oklahoma. Copy the URL and 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 (no period).
 

4

8

Create hyperlinks for the Tulsa (hyperlink text is Tulsa) and Lawton (hyperlink text is Lawton-Fort Sill) worksheets as well (in cell A3). Set the ScreenTips as Click to see weather history for Tulsa (no period) and Click to see weather history for Lawton (no period). Continue to use www.wunderground.com for the weather links. Check each hyperlink to ensure it works correctly.
 

7

9

Enter the following 3-D formula in cell B6 of the Summary worksheet:
Calculate the highest temperature from the three cities for 6/1. Copy the formula down the High column.

 

10

10

Enter the following 3-D formula in cell D6 of the Summary worksheet:
Calculate the lowest temperature from the three cities for 6/1. Copy the formula down the Low column.

 

10

11

Enter a nested IF function in cell C6 to determine which city had the highest temperature. Based on the result returned in B6, enter the city name as OKC or Tulsa or Lawton in C6. Remember to enclose city names in double quotation marks in the function. Use Help if needed to help you understand a nested IF statement.  Copy the formula down the High-City column.
 

6

12

Enter a nested IF function in cell E6 to determine which city had the lowest temperature. Based on the result returned in D6, enter the city name as OKC or Tulsa or Lawton in E6. Copy the function down the Low-City column.
 

6

13

Enter functions in the shaded Monthly Records section (below the daily data) on the Summary worksheet to identify the highest (in column B) and lowest temperature (in column D).
 

5

14

Enter nested INDEX and MATCH functions to identify the dates and cities for the respective highest and lowest temperatures.
 

5

15

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.
 

4

16

Save and close the workbook, and submit the file as directed.
 

0

Total Points

100