### Introduction

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

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

• `=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.

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.