Description

W6 assignment instructions

Here you have some weather data for 50 different U.S. cities covering 12 months from January to December. Columns C to N contain the temperature information where:

·         'M' stands for Month, and the M is followed by a number between 1 and 12 denoting the month of January to December. These are always in order of 1 to 12.

·         'H' stands for High, and the H is followed by a number that is either 2 or 3 digits, giving the average high temperature (Fahrenheit) for that city for that month.

·         'L' stands for Low, and the L is followed by a number that is either 1 or 2 digits, giving the average low temperature (Fahrenheit) for that city for that month.

The data is presented to you in alphabetical order by City.

In the SummaryHigh sheet extract the monthly high figure data (all the digits between the 'H' and the 'L') using appropriate Excel functions.

For your workings and calculations, you may use the remainder of the Data1 worksheet, the Workings_1 worksheet, or add new worksheets as you need.

Question: Which of the following combination of functions could be used to achieve this task (although you may have used a different solution)?

1-Left, Find
2-Substitue,Len,Right
3-Right,Len
4-Find,Mid

Question 2

Observe that the numbers are aligned to the left, this is because they are still text values. Alter the function used so the results are numeric (you will get some errors, ignore them for now.)

Question: Which function converts numbers stored as text to numeric values (without losing decimal place information)?

Type in the function name only.

Question 3

Converting the values to numeric caused several #Value errors. If you look carefully you will notice that the data has used a Ø instead of a 0. Adjust the formula to replace the Øs with 0s.

In row 55 calculate the highest temperatures for each month.

Question: What is the highest temperature for March?

Type in your number without spaces and special characters.

There are still two #VALUE errors for Portland. Identify what is causing the errors and adjust your formulas to correct them.

Question: Which function did you need to add?
Trim
Substitute
Clean
Text

Question 5

If you have successfully cleaned the data, you should now see the completed checksum values in row 57 of the SummaryHigh sheet. To help you self-assess if you have done the work correctly, the SummaryHigh Check Sum for January should be 259045.

Question: What is the CheckSum for June?

Type in your number without spaces and special characters.

Question 6

In the SummaryLow sheet extract the monthly low figure data (all the digits after the 'L' using appropriate Excel functions.

For your workings and calculations, you may use the remainder of the Data1 worksheet, the Workings_1 worksheet, or add new worksheets as you need.

Question: Which of the following combination of functions could be used to achieve this task (although you may have used a different solution)?

Find,Substitute
Mid,Len,Char
Right,Len,Find
Left,Find

As you did with the SummaryHigh figures, adjust your calculations so that they remove any unwanted characters, replace the Øs with 0s and convert the results to numerics, to get a completely clean data set.

To help you self-assess if you have done the work correctly, the SummaryLow Check Sum for January should be 172862.

Question: What is the CheckSum for August?

Now select the MonthSorted worksheet. Your task for the next few questions is to write some formulas so that when a month is selected from the drop down box at cell C3, the green cells in columns B and C will show each city and its High temperature for that month, but automatically sorted from largest value temperature to the lowest value temperature. So, for example, when February is selected Miami should be at the top and Minneapolis should be at the bottom.

If two or more cities have the same temperature, sort those equal cities by alphabetical order (A at the top, Z at the bottom).

Use columns K-N of the MonthSorted worksheet for your workings, and report the final answers in columns B and C.

In K2 use a function to get the appropriate column number from which to select the data for the selected month. For example, if the selected month is January, the formula should return 1.

Question: Which function is best suited to this task?

Match
Offset
Index
Vlookup