Due Tuesday, Nov. 3 by 8:00pm
In this MP, you will complete a spreadsheet to plan a multi-city road trip across America!
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.)
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).
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.
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.
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 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.
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.
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.