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

Get Higher Grades Now

Tutors Online