Homework 1-5

Due October 1, 2015, 9:00 am

Reminders

If a problem is marked as “(Independent)”, you may only discuss the problem with course staff. Otherwise, you are free to discuss the concepts that will help you solve the problems with classmates as well as course staff. However, you are never allowed to simply copy answers.

Other notes

For this following task, you will be sharing in a spreadsheet with the handin account - cs0931handinfall2015@gmail.com . In the text file please label your answers clearly so that we know which answers go with which question.

Task 1

Matrix multiplication

In class, we built a spreadsheet that took all the voting records (+1 for Yea, -1 for Nay, 0 for missing, etc.) and compared them pairwise. The comparison used the idea that if you multiply two voting records and sum up the resulting products, you get the "agree minus disagree" number. (If you take absolute values before multiplying, you get "agree + disagree" instead). This notion of sums of pairwise products comes up over and over again in mathematics, economics, etc. It's almost never done with pairs of rows from a table, but instead with a row from one table and a column from another. When we do this, each table (which should be a table of numbers) is called a matrix (plural: matrices), and the process of computing all possible row-column sum-products is called "matrix multiplication." You're going to play with that a little bit in this problem.

  1. Make a copy of the HW 1-5 spreadsheet. On it, you'll see matrix A, in pink, whose rows are labeled with the names of people, and whose columns are labeled with items for purchase. Robin is evidently planning to buy more stuff than Mary is. You'll also see matrix B, a table of prices (in dollars per item) for the three items, for each of two months. Notice that the number of COLUMNS of A is the same as the number of ROWS of B.

    Here is a description of how to compute the matrix product A*B:
    We place the matrices as shown in B12 through F16: the first one to the left, the second one up and to the right. We then fill in the space (marked in green) in the lower right. Each cell in the green space lies in the same row as some row of matrix A and in the same column as some column of matrix B. We place in this cell the SUMPRODUCT of that row and that column. When you use SUMPRODUCT, you can pass either two rows or two columns. To have the function to work with a row and a column, use the TRANSPOSE function in one of the arguments, transforming a row into a column or a column into a row. Your function should look like:

    SUMPRODUCT("row", TRANSPOSE("column"))
    or
    SUMPRODUCT(TRANSPOSE("row"), "column")

    where "row" and "column" are specified in the matrices.
  2. Do that now for the upper left corner of the green area, and use fill handles to extend the definition to the rest of the green area. The SUMPRODUCT formula you wrote wasn't too complex, but there's a far simpler way to produce the same answer.
  3. In the second area (rows 20-24), enter, in cell E23, the formula = MMULT(something, something else) where the "something" is the range that defines the pink matrix, and the "something else" is the range defining the blue matrix. The results should be the same as those in the green area above.
  4. MMULT doesn't actually require that its two arguments (the things inside the parentheses) be placed in the way we placed them for the SUMPRODUCT computation: they can be anywhere. You'll see that in E7 to F8, we've made another green box. Enter a formula in E7 that will multiply the two original matrices (just to the left of the green box) and produce a result that should be the same as the one you got for the last two steps.

Task 2

Meaning of matrix multiplication

If we write the ROW labels for matrix A and the COLUMN labels for matrix B near our green matrix, the top left corner is "Robin, July". What does the number 20 that's in that spot mean? Well, it was computed as (1 x 2) + (2 x 3) + (3 x 3) so it's the total cost for Robin to buy her basket of goods in july: 1 soap at $2, 2 eggs at $3, and 3 pears at $4. (Expensive pears!)

  1. At the bottom of the page, fill in the two yellow cells by looking up the appropriate values in any of the green arrays.

Task 3

Experiments with coin flips, making plots, etc.

Open the "Random Coin Flips" tab of the spreadsheet. Using the RANDBETWEEN() function, we've made 25 rows of 8 random numbers each, representing coin-flips: 0 is heads, 1 is tails. You're going to make a summary of these results. You might expect that if you flip a coin 8 times, on average it'll come up heads four times.

  1. Test that conjecture by entering, in column I, a formula to compute how many heads you got in each 8-flip trial. It'd be best if you entered just one formula in cell I2, and then filled down, of course.
  2. What's the smallest number you could possibly get in column I? (Hint: zero). The largest? Fill in your answers in the spaces provided.
  3. In cell A31, enter the word "Value", and beneath it enter a list of the possible values, from smallest to largest, starting with the smallest in cell A32.
  4. In cell B31, enter the label "Value Count", and in cell B32, enter a formula to count how many times you got exactly zero heads in the 25 experiments. (For "0", use a reference to cell A32). Fill that formula down, and see whether it gives you the counts of how many times you got 1, 2, 3, etc. heads. Hint: if you use =A32 as your condition in a COUNTIF, it won't "fill" correctly, because the cell reference is inside a string, and gets ignored. If you just type A32, it works fine, though.
  5. In cells A31 to B40 (more or less), you've got some data. Try to figure out a way to make a graph of that data, with the "values" on the x-axis and the value-counts on the y-axis. It could be a line graph, or bar graph, or anything else.

Task 4

Practice with "scraping" data and "polishing" a spreadsheet

Some words are more common than others; a quick way to tell is to do a quick Google search. "Synchronicity" has about 5 million results, while "Diachronicity" has only about 9000.

Open a new tab on your spreadsheet, and in that tab, build a spreadsheet that lets a viewer enter two words and then shows which one has more results in a Google search.

To find the URL, you'll do a Google search for some word and see what the URL is, and then try to alter that to handle other words. If you go to google.com and type your query there, the URL of the results page should look like
https://www.google.com/search?q=synchronicity
But beware: if you type your query directly in the address bar (in Chrome), the resulting URL will look like this:
https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8
#safe=off&q=synchronicity
or possibly something even more complex. Use the simpler form. Google Spreadsheets doesn't seem to be able to read from the more complicated URL for the results page.

Your spreadsheet should have the following:

Handin

Share the following spreadsheet with cs0931handinfall2015@gmail.com

  • FirstLast_HW1-5

    • In the above file name, "FirstLast" should be replaced with your first and last name. If it is not, we will take off points. Make sure every task has been completed.