Homework 1-2

Adapted from Brown University CSCI0930. Used with permission.

Reminders

For the following problems you may discuss the concepts that will help solve these problems with classmates and course staff. You may not simply copy down the answers of your classmates as that is a violation of the collaboration policy. The one exception to this rule are those problems marked as “(Independent)”. You may discuss independent problems with course staff only.

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 again, be helpful.

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

The Row() Function

Task 1:

  1. Create a new Google spreadsheet and rename it as Name_StudentID_HW1-2Part1.
  2. Rename Sheet1 to 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.
  3. 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.

  4. Experiment with Column(); describe its behavior.
  5. 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).
  6. 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.

  7. 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 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 2:

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

  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 Name_StudentID_HW1-2Part1.
  8. You will share this copy with the COMP1D04 gmail account at the end of this assignment.

Task 4:

  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. Bonus (Independent Question): Next, we want to use conditional formatting to color in the cells on the original sheet that contain grades higher than Anne's score on that same assignment. Now this is not too easy. We need to write a conditional formatting formula for cells B2:G7 on Sheet1. The formula should look at the corresponding cell on Sheet2. If the corresponding cell is a 1, then we want to color it. We'll give you some hints:
    • Experiment with the address() command. In cell A10, type =address(1, 2). What is the result? In Cell B10address does.
    • Experiment with the indirect() command. In cell A11, type =indirect("A1"). What is the result? In Cell B11, write down in your own words what Indirect does.
    • Now we're ready to put everything together.
      1. Under Format, go to Conditional Formatting, and bring up the Conditional format rules.
      2. Start a new rule. Your rule should apply to all the numerical scores of the students in the Grade sheet.
      3. You want to use a Custom Formula. Your formula should check whether the corresponding cell in Sheet2 has a 1 in it. You will need to use &, address, row and column to generate the reference for the cell that you wish to check, and wrap indirect around it.
  4. Rename a copy of this file as Name_StudentID_HW1-2Part2.
  5. You will share this copy with the COMP1D04 gmail account at the end of this assignment.

Final Handin

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

  1. Name_StudentID_HW1-2Part1
  2. Name_StudentID_HW1-2Part2
In the above file names, “Name” should be replaced with your official name and StudentID with your studentID or we will take off points.