CSCI 0931

Intro Comp for Humanities & Social Sciences

Alexandra Papoutsaki

Due September 17, 2015, 9:00 am

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.

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.

- 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. - 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)`

. - 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! - 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! - 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.) - Finally, save your file as
`FirstLast_HW1-2Part1`

.

`Row()`

Function- 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. - 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`

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

`Match()`

FunctionThe 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)`

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`

).

- 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`

. - 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. - In cell
`G17`

, put a formula to count the number of ones in`A17:F17`

.

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

- Since there are three
`Q`

s in the table, enter the numbers`1`

,`2`

, and`3`

in cells`A20:C20`

. - 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.) - In cells
`A22:C22`

, enter a formula that subtracts one from the corresponding entries in cells`A21:C21`

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

. - 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. - Explain the use of dollar signs in the formula
`=Offset($A15, 0, A$22)`

as a note in cell`A24`

. - Save a copy of this file (which now includes both the StockReport and FillStuff sheets) as
`FirstLast_HW1-2Part1`

. - You will share this copy with the TAs at the end of this assignment.

- Make a copy of
`GradeSheet`

and save it to your own Drive. - 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! - Use conditional formatting to color in the cells on the original sheet that contain grades higher than Anne's score on that same assignment.
- Rename a copy of this file as
`FirstLast_HW1-2Part2`

. - You will share this copy with the TAs at the end of this assignment.

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.

`FirstLast_HW1-2Part1`

`FirstLast_HW1-2Part2`