COMP1D04

From Scratch to Apps: Foundations of Computational Thinking and Literacy for Problem Solving

Grace Ngai

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

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.

`Row()`

Function- Create a new Google spreadsheet and rename it as
`Name_StudentID_HW1-2Part1`

. - 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. - 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 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
`Name_StudentID_HW1-2Part1`

. - You will share this copy with the COMP1D04 gmail account 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! **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.
- Under
**Format**, go to**Conditional Formatting**, and bring up the Conditional format rules. - Start a new rule. Your rule should apply to all the numerical scores of the students in the Grade sheet.
- 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. - Rename a copy of this file as
`Name_StudentID_HW1-2Part2`

. - You will share this copy with the COMP1D04 gmail account at the end of this assignment.

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.

`Name_StudentID_HW1-2Part1`

`Name_StudentID_HW1-2Part2`