Introduction

In this MP, you will complete a spreadsheet to plan a multi-city road trip across America!

Download the Files

Download the Excel (.xlsx) file for MP5

MP5 Walkthrough

Question 1: Your Unique Cities

In MP5, you will work on your own unique set of cities. You must complete your MP with your cities. (Since the grader will be expecting your cities, if your MP contains other cities than the ones assigned to you then you grade will almost certainly be very close to or exactly 0/30. Make sure to grab your cities.)

Get your cities for MP5

Question 2: Using Data from Another Sheet

In Quesiton 2, you are asked to find the distance between Chicago and the four other cities that you are working with. In order to do this, you must write a formula that looks a data from another sheet within your Excel workbook.

At the bottom of your Excel worksheet, you will find a series of tabs labeled "Overview", "Data", and "Permutations":

(The exact user interface varies between versions of Excel, but all versions show all three sheets in the same order and work the exact same way.)

Clicking on the "Data" tab brings up the "Data" sheet. On this sheet, you will find the distances between all 31 cities in our data set.

Moving back to the "Overview" sheet, you can refer to cells on the "Data" sheet by using a cell reference that includes a sheet name. The syntax for this in Excel is to include the name of the sheet followed by an exclamation mark (!) and then followed by a cell reference for the sheet you just specified. For example:

• =Data!A1 will return the value of the cell A1 on the Data sheet. On the MP5 spreadsheet, =Data!A1 returns Anchorage.
• =MIN(Data!C1:C930) will return the minimum value in the range C1:C930 on the Data sheet. On the MP5 spreadsheet, =MIN(Data!C1:C930) returns 8 (the distance between Oakland and San Francisco is only 8 miles). We could also write this as =MIN(Data!C:C) since we are interested in all of the data in Column C.

To complete Question 2, you must write a formula that uses the Data sheet to find the distance between Chicago and your four other cities. This formula can be written with a SUMIFS that contains two criteria (you will use both criteria1 and criteria2).

• You should manually look at the Data sheet to find out what the answer should be based on your city. Since you could have any one of 30 cities there, the correct answer is going to differ based on what city ou have been assigned.
• Once you have a formula that works for your first city written in D19, you must lock specific rows and/or columns with the $ symbol so that, when you copy and paste cell D19 into D20:D22, the answers are correct in D20:D22. If you manually edit the formula in D20, D21, or D22, your formula will be graded as incorrect.

Questions 3-10

Continue using your arithmetic operations and arithmetic functions to complete the next seven questions. The answer to all questions must be a formula to solve for the answer and must not a static answer.

Many of the questions require a formula that must only be written once and then copied and pasted to fill in other cells. Just like Question 2, editing the formula in the other cells to get it to work is incorrect; you must find the proper rows/columns to lock in your formula.

Question 11

Question 11 requires you to use a new Excel function, MATCH. In Excel, MATCH takes a value (lookup_value) and finds it within a range of data (lookup_array) and returns the index where the value was found in the range. The value of match_type must be set to 0 for this assignment. The full function definition for MATCH is:

=MATCH( lookup_value, lookup_array, [match_type] )

Unlike JavaScript, Excel uses an index system starting from the number 1. Using the spreadsheet to the right:

• =MATCH( "Hello", A1:A3, 0 ) returns 1, since "Hello" is found in the first cell in the range A1:A3.
• =MATCH( 105, A1:A3, 0 ) returns 3, since 105 is found in the third cell in the range A1:A3.

Using the MATCH function, complete Question 11.

Questions 12-15

Finally, questions 12-15 uses one final Excel function: INDEX. The INDEX function does the complete opposite of MATCH: given a range (array) and a row index (row_num), INDEX returns the value of the cell at the given row in the range. INDEX can optionally take a column_num as a third argument, but will default to the first column of the data if none is given. The full function definition for INDEX is:

=INDEX( array, row_num, [col_num] )

Just like MATCH, INDEX uses a index/number system starting from 1. Using the same spreadsheet as an example (see Question 11):

• =INDEX( A1:A3, 1 ) returns Hello, since "Hello" is found in the first cell in the range A1:A3.
• =INDEX( A1:A3, 3 ) returns 105, since 105 is found in the third cell in the range A1:A3.

Using the INDEX function, complete Questions 12-15.

Grading and Submission

Grading

Your MP must be in Excel's XLSX format. We are unable to accept any other formats. If your file does not open using Excel 2010+, it will not be graded and you will receive a 0/30. We will not open/grade it with Numbers, Google Sheets, or any other program.

Once we are able to open your MP, we will grade each of the 15 questions independently. The simpler questions (Q1, Q5, Q6, Q7, Q10, Q13, Q14, and Q15) are worth 1 point each, the most complex question (Q3) is worth 4 points, and all other questions are worth 3 points each. Your answers must appear in the original locations on the spreadsheet (eg: Q2 in D19:D22, Q6 in D55, Q14 in D124, etc); do not add any rows or columns.

Submission

To submit your MP, you must submit your completed Excel file to the CS 105 website. Ensure you save your file before uploading it.

Submit your MP5