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

Download the Files

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.)

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:

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).

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:

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):

Using the INDEX function, complete Questions 12-15.

Grading and Submission


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.


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