1. Question 1 The assignment contains two parts. Part 1 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...

1. Question 1
The assignment contains two parts.
Part 1
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)?
FIND, MID
RIGHT, LEN
LEFT, FIND
SUBSTITUTE, LEN, RIGHT
1
point
2. 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 e
ors, 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.
3
points
3. Question 3
Converting the values to numeric caused several #Value e
ors. 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.
Question 4
1
point
4. Question 4
There are still two #VALUE e
ors for Portland. Identify what is causing the e
ors and adjust your formulas to co
ect them.
Question: Which function did you need to add?
TRIM
SUBSTITUTE
CLEAN
TEXT
2
points
5. 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 co
ectly, 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
2
points
6. 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)?
MID, LEN, CHAR
RIGHT, LEN, FIND
FIND, SUBSTITUTE
LEFT, FIND
7. Question 7
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 co
ectly, the SummaryLow Check Sum for January should be 172862.
Question: What is the CheckSum for August?
Question 8
1
point
8. Question 8
Now select the MonthSorted worksheet. Your task here 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 Fe
uary 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?
OFFSET
MATCH
VLOOKUP
INDEX
Question 9
2
points
9. Question 9
In column K use a formula (with the help of the value in K2) to retrieve the high temperature for the co
esponding city in column J and the selected month.
Question: Which function would be best suited to this task?
INDEX
VLOOKUP
INDIRECT
MATCH
1
point
10. Question 10
Where the temperatures are the same we need to sort by City alphabetically, we can take advantage of the fact the cities are cu
ently co
ectly sorted so the higher the row number the further down they should go. In column L calculate an adjusted value by subtracting 1000th of the row number from the temperature (i.e. temperature - row numbe
1000).
Change the selected month to March, then in a blank cell calculate the sum of the adjusted values.
Question: What is the sum to 3 decimal places?
Enter the answer below using a dot for the decimal point - e.g., XXXXXXXXXX
Question 11
1
point
11. Question 11
In column M use an appropriate function to rank each adjusted value against the rest of the set.
Question: What is the sum of the co
ect rankings for Las Vegas, Los Angeles & Louisville (for March)?
47
48
49
50
2
points
12. Question 12
In column N calculate the sort order using the numbers in column A and the rankings in column M. In other words in N5 you should end up with the value 24 as the 24th value in the list (Miami) has the highest temperature for March.
Question: What is the sum of the rankings for Memphis to Portland (N27:N39)?
Question 13
2
points
13. Question 13
Use the calculated sort order to populate columns B and C with the cities and temperatures in the co
ect sort order.
Question: Which of the following functions would co
ectly retrieve the city with the highest temperature:
=INDEX(J$5:J$54,$N$5)
=INDEX(J$5:J$54,MATCH(A5,$N5:$N54,0))
=INDEX(J$5:J$54,MATCH(A5,$N$5:$N$54,0))
=INDEX(J$5:J$54,$N5)
2
points
14. Question 14
What is the CheckSum for the City (in B56)?
1
point
15. Question 15
Congratulations, that is the end of Part 1.
Part 2.
Select the Scenarios worksheet. The information about the task is presented in the worksheet. Read it, and when you are ready, write the formulas you need in order to populate the green cells at F54:G83.
When you are done, answer the questions below.
Make sure to submit all of your answers rounded to the nearest dollar (which should be how they are displayed by Excel), but without any dollar signs or other punctuation.
For example, if you were asked for the value in cell G54, which is $13,171.64, you would write "13172".
(Hint: This is indeed the co
ect value for cell G54. Use it to check your work.)
Question: What is the value of the Initial Deposit for Account Number 2?
1
point
16. Question 16
What is the value of the Initial Deposit for Account Number 5?
1
point
17. Question 17
What is the value of the Final Account Balance for Account Number 11?
1
point
18. Question 18
What is the value of the Final Account Balance for Account Number 20?
3
points
19. Question 19
What is the sum of the Initial Deposits for all of the accounts? (i.e. the value of the Check Sum at cell F85)
3
points
20. Question 20
What is the sum of the Final Account Balance for all of the accounts? (i.e. the value of the Check Sum at cell G85)