Homework 1-2

Due September 17, 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.

Basics

As in the last assignment, these problems should take you just a few minutes each. Each one covers a particular skill that you'll need to have with spreadsheets. Most involve a task and some hint. You'll want to do a web search, or ask a friend, or use the Help menu from the toolbar to learn.

This Google Spreadsheets tutorial will be helpful.

  1. Tasks 1-4 will complete Part 1 of your handin.
  2. Task 5 will complete Part 2 of your handin.

Task 1:

  1. Create a new Google spreadsheet and rename Sheet1 to StockReport. We will now use the IMPORTHTML() command to import a table of daily stock data from this website into your spreadsheet. The last part of the URL, GOOG, refers to the stock symbol that you wish to see the price for (in this case Google). Feel free to change this to any other ticker-symbol you'd like.
  2. IMPORTHTML() is a little clunky, but it's very powerful once you figure it out. It takes three arguments. The first argument is the url of your website (in double quotes). The second argument must be either "list" or "table" and determines whether you're asking for a list or a table; we want a table so use "table" (once again in quotes). The last argument is an index defining which table from the URL to pull, starting at 1. Your resulting command will look something like this: =IMPORTHTML("full website name", "table", table_number).
  3. The "most recent activity" table is the fifth on this web page, so go ahead and try importing the table into cell A1. Congratulations, you scraped data from the internet!
  4. But stock prices aren't very useful once they age — we need to update our table to stay current. Using File > Speadsheet Settings, change the Recalculation of your spreadsheet to refresh every minute even when you're not typing anything. This will make Spreadsheets check the source webpage every minute to see if anything in the table has changed, and hopefully you will see the stock price being updated dynamically!
  5. Use Format > Conditional Formatting to make the background of the “Close/Last” cell be pale green if the price rose throughout the day (which means the value in the close cell—which holds the close price from the previous day—is less than the open price from that day, i.e. the value in column E is less than the value in column B), pale red if it fell and yellow if the price hasn't changed. Look at the tutorial lesson on conditional formatting and try practicing on just a few cells if you can't remember how to do this. (Hint 1: you will need more than one conditional formatting rule. Hint 2: use custom formulas to refer to other cells, remembering to precede your formula with the equals sign. Hint 3: get your formulas right for the first cell, then go back to the Conditional Formatting window and change the range to which your rules should apply, using the "<first>:<last>" notation. Filling of the conditional rule will work as expected.)
  6. Finally, save your file as FirstLast_HW1-2Part1.

The Row() Function

Task 2:

  1. Create Sheet2 of FirstLast_HW1-2Part1 and name it FillStuff. Put the number 7 in cell A1 and 8 in cell A2. Use the fill handle to fill in the numbers 8, 9, 10, ... 16 in rows 2 through 10.
  2. Enter the formula =Row() + 6 in cell B1. Fill down for ten rows. Your results should be the same as those in column A.

    Explanation: The value of the Row() function in a cell is the number of the row that the cell is in. So cell B1 is in row 1, hence Row() + 6 has the value 7.

  3. Experiment with Column(); describe its behavior.
  4. In cell C1, enter the formula =A1 + Row(). Before you do so, predict what will happen when you fill down from cell C1 to C10, and write your prediction in a note for cell C1. Now execute the fill, and in a note for cell C10, explain what happened (even if it agrees with your prediction).
  5. In cell D1, enter the formula =A$1 + Row(). Again, predict the results of filling down, and explain the results you actually get, using notes on column D instead.

  6. Point of Information: Absolute and Relative References

    Work through the tutorial pages to learn more about the differences between what we've done in points 4 and 5. Here is an explanation of relative references and here is an explanation of absolute references. We'll see these a lot, so make sure you have a good understanding of the differences between them.

The Match() Function

The Match() function finds the location of a particular item in a row or column of items. If the item appears more than once, then Match() reports the first occurrence, at least if you set the third parameter to 0, which is what you should do in all these examples.

For example, if you want to find the first cell that contains the exact text “Test tube” in cells A1:A9, you would type: =Match("Test tube",A1:A9,0)

Point of Information: Cell Arrays

In the syntax above, A1:A9 means that you should look at the range of cells in column A going down from row 1 to row 9 (i.e., cells A1 through A9). This linear column of cells is also referred to as an range. We've seen this before; when you used the fill handles in Task 7, you were copying values over a range of cells.

Many functions in speadsheets only make sense to use when we are computing some value over a range of cells. For example, a simple use of a cell array would be to give it as a parameter to the SUM function. This function will look at the range you have given it, and add all of the numbers together. So, if we were to enter the formula SUM(A1:A9) in a certain cell, the output will be the sum of all of the values in the cells over the range A1:A9. Ranges can be a single cell, a single row (A1:G1), a single column(A1:A10), or over multiple rows and columns (A1:G9).

Task 3:

  1. In cells A15:F15 of FillStuff, enter P, Q, P, Q, Q, R. In cells A16:F16, enter 15, 13, 12, 6, 3, 9. In cell A17, enter a formula that produces a 1 if A15 has a Q in it, and a zero otherwise. Fill this to the right for six cells. In cell H15, enter a formula involving Match() that will tell which column in row 15 is the first to contain the letter Q. In cell H17, enter a formula involving Match() that will tell which column in row 17 is the first to contain the number 1.
  2. In cell A18, enter the formula =A17, which will copy the zero from the row above. In cell B18, enter the formula =A18+B17; this takes the previous result and adds to it the next entry from row 17. Fill this formula to the right from B18 to F18. The resulting values are called the partial sums of the numbers in A17:F17. You'll notice that the partial sums increase by one exactly in the columns where the letter Q appears in row 15. Observe what happens if you use Match() to locate the numbers 1, 2, and 3 in row 18.
  3. In cell G17, put a formula to count the number of ones in A17:F17.

The Offset() Function

The Offset() function can be used to grab a particular cell by saying “it's the one 2 steps down and 3 steps over from some other cell.” For instance, the formula =Offset(A1, 2, 3) will give the value in cell D3: from A1, we move down two rows to get the location A3, and then move three columns to the right to get to D3. Negative row and column offsets move up and left, respectively.

We're going to use Offset to copy exactly those columns of our original data where there's a Q in row 1. This would be easy to do by hand, of course — there are just three columns, and we could just copy them one at a time. But when you get to 200 columns, that becomes impractical. Here's the idea:

In row 18, we've got a sequence in which each new number first appears in a row containing the letter Q. The first 1 is in column B; the first 2 is in column D; the first 3 is in column E. If we use Match to find these columns, we can copy them to a new location.

Task 4:

  1. Since there are three Qs in the table, enter the numbers 1, 2, and 3 in cells A20:C20.
  2. In cell A21, enter a formula using Match() that identifies the column in which the first 1 appears in row 18. Don't use the number 1 directly; instead, get it from cell A20. Fill your formula two more cells to the right, so that cells B21 and C21 contain the columns of the first appearance of the numbers 2 and 3, respectively. (Hint: when entering the range to search, you should use $A18:$F18, so that the same range will be used when you fill right, rather than the range being adjusted to the right as well.)
  3. In cells A22:C22, enter a formula that subtracts one from the corresponding entries in cells A21:C21.
  4. In cell A24, enter the formula =Offset($A15, 0, A$22). Because A22 contains the number 1, this goes to cell A15, moves down zero rows, and across by one column; it copies the value in cell B15, which is Q. Now enter a formula in cell A25 that copies the data in B16 by offsetting from cell A15.
  5. Would Fill Handles have worked to make the second formula in the previous task? Use Fill Handles to fill in formulas for cells B24:C24 and B25:C25. You've succeeded in copying all columns from the original table that had a Q in the first row.
  6. Explain the use of dollar signs in the formula =Offset($A15, 0, A$22) as a note in cell A24.
  7. Save a copy of this file (which now includes both the StockReport and FillStuff sheets) as FirstLast_HW1-2Part1.
  8. You will share this copy with the TAs at the end of this assignment.

Task 5:

  1. Make a copy of GradeSheet and save it to your own Drive.
  2. Make a second sheet, containing a table of the same size and shape. For this table, make every cell show either a 0 or a 1. A cell should show a 1 if the score is higher than Anne's score and a 0 if it's equal to or less than Anne's score. This is tricky because of the need for absolute addressing in one index!
  3. Use conditional formatting to color in the cells on the original sheet that contain grades higher than Anne's score on that same assignment.
  4. Rename a copy of this file as FirstLast_HW1-2Part2.
  5. You will share this copy with the TAs at the end of this assignment.

Final Handin

Congratulations, you're done with your second assignment! Share the following files with cs0931handinfall2015@gmail.com . Make sure all your submissions have your name in the file name.

  1. FirstLast_HW1-2Part1
  2. FirstLast_HW1-2Part2
In the above file names, “FirstLast” should be replaced with your first and last name or we will take off points. Make sure every task has been completed.