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 the teaching team only*.

These problems should take you just a few minutes each; each one aims to teach you a particular skill that you'll need to have with spreadsheets. Most involve a task and some hint of how to approach it; you'll want to do a web search, or ask a friend, or use the Help menu from the toolbar to learn how to do things.

One good reference is this google spreadsheets tutorial. In fact, several of the following problems involve parts of it; if you have some time, it is definitely worth working through the whole thing over the next week or two.

This homework may seem long, but don't get intimidated! Most of the assignment is a tutorial, and the problems themselves are very easy. If you are already comfortable with spreadsheets, feel free to skim over the tutorials and focus mainly on anything that you are not familiar with. There is also a required handin at the end of the assignment that will test most of the subjects covered in this tutorial.

- Tasks 1-9 will give you an introduction to spreadsheets and serve as background for later tasks.
- Task 10 will complete the first file of your handin.
- Tasks 11-14 will complete the second file of your handin.
- First enter your name in cell
`A1`

. Then click on cell`B1`

, and in the formula bar, typeand hit=A1

`Enter`. The equals sign is essential; it tells the spreadsheet that you're entering a formula rather than just a piece of text like your name. When you hit`Enter`, cell`B1`

should show your name, just as cell`A1`

does. Now click on cell`C1`

and in the formula bar, type(notice that there's no equals-sign). PressA1

`Enter`. Cell`C1`

should display the text “A1”. - Now go to cell
`A1`

and enter a different name. Notice that cell`B1`

also changes. That's because the rule defining`B1`

is that it “equals whatever's in`A1`

”. The rule defining`A1`

is that it's “Robin Smith” (or whatever you typed in there). Change cell`A1`

back to your own name. - Suppose that for some reason, you wanted cell
`C1`

to contain the letters “=A1” (that is to say, an equals sign, followed by a capital A, followed by the numeral “1”). If you tried to type this into cell`C1`

, the spreadsheet would see the equals sign and interpret it as a formula. What can you do? This is a special case: you can type a single quote, and then “=A1”, i.e., you enterand it'll turn it into what you want. You'll probably never need to do this, but you should try it once just to see that it works.'=A1

This homework will also be a great reference for you to look back on as we progress through the course, because there are many points of information and hints that may be helpful in later assignments, so keep that in mind!

Go to the tutorial and read through "Google Sheets" Lessons 8-11. When you're done, you should know how to move around in a spreadsheet, enter data into cells, delete data from cells, select a region of cells, how to save and close a spreadsheet, and even how to format cells.

Now we're going to enter a formula in a cell. Formulas are the heart of spreadhsheets, but for now we're doing something very simple.

At the lower-left of the spreadsheet, you'll notice a tab labelled “Sheet1”. When your spreadsheet opens up, you're looking at `Sheet1`

, and more sheets can be created by clicking the `+`

button next to the sheet name. Enter your name in cell `A1`

(it may still be there from Task 2). Now click the `+`

button; you'll see a fresh spreadsheet in which cell `A1`

is empty. Enter a friend's name in cell `A1`

. Click on the `Sheet1`

tab, and then on the `Sheet2`

tab.

In cell `A2`

in `Sheet1`

, decribe what you see in cell `A1`

after each click.

If you go to `Sheet2`

, and in cell `B1`

you enter `=A1`

, the value that will appear in cell `B1`

will be your friend's name. That's because cell-addresses refer to the current sheet. But you *can* enter a formula that lets you get at cells from another sheet. On `Sheet2`

, click on cell `B1`

. Enter the formula

=Sheet1!A1

`B1`

, having been copied from cell `A1`

on `Sheet1`

. Go to `Sheet1`

, cell `A2`

, and enter your age. Notice that it appears all the way to the right in cell `A2`

because your spreadsheet has interpreted it as a number, and numbers, by default, are shown "right justified" in spreadsheet cells.
Naming things is one of the ways that we impose structure on the world. While google spreadsheets has default names (like “A1” or “N13”) sometimes it makes sense to give things names that are more readable by humans. For instance, the contents of cell `A2`

(your age), might be better called “age” than “A2.” Here's how to add a name to a cell:

- On the menu at the top of the screen, click data, and then select the button that says "Named ranges..".
- Click "Add a range" of the new window that just popped up. The speadsheet will prompt you to enter a name, and choose one or more cells that the name will refer to. Use "age" for the cell name and to specify the cell, use "Sheet1!A2". Now click "Done". Cell A2 is now named "age".
**Big Efficiency hint:**Use the`Tab`

key to quickly select different different spaces. In this case, after you click the`+`

to add a new range, you will have the name selected. After you enter in a name, using`Tab`

will select the range for you, and using it again and pressing`Enter`

will click the little grid in the second text box, allowing you to manually select cells from the spreadsheet to name! After you select your cell(s) and click`ok`

, tabbing two more times will allow to hit`done`

with one last`Enter`

key. This all might seem weird and a little pointless, but realize that tabbing can be used in most applications, and the seconds you save in repeated processes from not moving your hand between mouse and keyboard will add up quickly. Also, if you overshoot something, use`Shift + Tab`

to go backwards. - Click on
`B2`

, and enter the formula`=age`; the result should be that your age appears in cell`B2`

. - Click on cell
`B2`

and enter the formula`=A2`; again, your age should appear. The cell`A2`

can be referred to either by its standard spreadsheet address (A2), or by its name (“age”). - Go to
`Sheet2`

, and in cell`B2`

, type a formula that will copy your age from`Sheet1`

, cell`A2`

. Use the name “age” rather than “A2”.

Click on the `Sheet3`

tab. Then click on the small arrow on the right of the tab. A menu pops up; click `Rename`

and rename it “Junk”. You'll see the name change on the tab. The same can also be accomplished by double-clicking on the tab name. Try this, and change the name back to “Sheet3”.** Hint:** Don't name sheets with spaces, otherwise referring to them in your formulas will become ugly. Use names like "My_Finances" instead of "My Finances".

When you build a spreadsheet, you're doing so with some *intent*. You should record your intentions by adding notes to your spreadsheet(s).
### Task 6:

Right click on cell `A2`

of `Sheet1`

. Select `Insert note`

. Type `Enter your age in this cell` as a note. Notice:

- A small black triangle appears in the upper right of the cell to indicate there's a note for the cell.
- When you hover your mouse cursor over the cell, the note appears.

Let's talk about “Filling” — taking some data from a cell or two, and using it to generate data for a whole bunch of cells. Filling is kind of like a fancy copy/paste operation. We'll do this by example in the next task.
#### A Useful Side Note: Keyboard Shortcuts

### Task 7:

#### Another Side Note: More Keyboard Shortcuts

### Task 8:

### Task 9:

### Task 10:

By the way, cutting, pasting, and copying work in spreadsheets much as they do in many other programs, with a few subtle differences that you'll learn about.

A useful shortcut is to highlight whatever it is you wish to copy, and then press the `Ctrl` and `C` keys on the keyboard to copy the text (On Macs, use `Command` instead of `ctrl` for most of these instructions). Then move your cursor to wherever you wish to paste, and hit the `Ctrl` and `V` keys to paste. If you haven't done this before, try it out in any program, even while writing an email, for example. Keyboard shortcuts are extremely useful and once you learn them, they will save you a lot of time!

Now let's try copying and pasting in a way that is unique to spreadsheets: by filling.

- Review the next portion of the tutorial on using “Fill Handles.”

There are also keyboard shortcuts involved with filling! If you highlight all the cells you want to fill (including the initial one) and press the `Ctrl` (or `Command` if you're on a Mac)and `Enter` keys, the selection should automatically fill down and/or right for you.

If you would like to select mulitple cells using only the keyboard, there are shortcuts for that, too. If you've selected a cell and want to highlight a series of cells below that, pressing the `Shift` and the `down arrow` (or the arrow of whatever direction you want to highlight) for however many cells you want to highlight will do that for you. If you want to select multiple cells that aren't necessarily grouped together on the spreadsheet, you can do this by holding down the `Ctrl` key and clicking on all the cells you want to highlight.

Finally, if you want to highlight a whole range of occupied cells, pressing `Ctrl`, `Shift`, and an `arrow` key will automatically highlight all the occupied cells in the arrow's direction from the initial cell, i.e. if there are 15 occupied cells grouped together in a column, if you select the top cell and press `Ctrl`, `Shift`, and the `down arrow`, all 15 cells will be automatically highlighted. If there are no occupied cells in the direction of the arrow key, you'll end up highlighting all the blank cells to the end of the sheet/to the next occupied cell.

If you would like to learn more keyboard shortcuts for Google Spreadsheets, here's a handy guide you can look at

Read through the tutorial's “Lesson 12: Creating Simple Formulas”.

There's great power in establishing a bunch of relationships, and then seeing what happens when you change one cell. (“What would the company profits be if we could reduce the cost of manufacturing widgets by three percent?”) The formulas you've already encountered let you do things like that. But one kind of formula adds a special power to a spreadsheet: the conditional formula, which usually involves an `IF`

.

Here's how it works: suppose you are giving your friend directions on how to get to PolyU from the airport. You might say something like “Take the A22 from the airport, get off at Wylie Road and then walk the last 1.2 km. If it's rush hour and you don't want to get stuck in traffic, take the Airport Express to Kowloon station and then take a taxi from there. ” This is a conditional description: what your friend should do depends on whether or not it is rush hour. So rush-hour-ness is the condition, and there are two choices for what to do: one if it is rush hour, and one if it isn't.

In writing expressions in speadsheets, we don't *do* things so much as we *express computations*: for example, “Cell `A3`

should be the sum of cells `A1`

and `A2`

”. How can a notion of conditionality fit into this? This isn't something you see in algebra class, so we have to write it a little differently. Let's see an example:

In cell

`D1`

, enter`12`

. In cell`D2`

, enter the formulaObserve the result. Change the value of=If(D1 > 10, 3, 0)

`D1`

to`4`

, and observe the result.#### An Explanation of Conditionals

The

`If`

expression checks to see whether`D1`

is more than`10`

; if so, the value of the`If`

expression is`3`

; if not, its value is`0`

. The “condition” (the bit of code before the first comma) can use equality-testing (`If(D1 = 3, ...)`

), various inequalities (`If(D1 >= 2, ...)`

,`If(D1 < 5, ...)`

), or even other functions (`If(IsBlank(D1), ...)`

). This last test determines whether cell`D1`

has anything in it or not, using the ISBLANK() function.The value-if-true can also be more complicated than a single number. For instance, the expression

gives=If(D1 < 5, D1+3, D1-2)

`D1+3`

if`D1`

is less than five, but if it's five or more, the expression gives`D1-2`

.#### Nesting Conditionals

But what if we want something different to happen when

`D1 = 5`

Now we have 3 desired outcomes, and a single`IF`

isn't enough to handle this. What do we need to change to fix this? Consider the problem. We effectively want to split the "is not less than 5" answer into "is equal to 5" and "is greater than 5". What we want is another`IF`

statement inside of our first`IF`

statement. Putting functions inside of each other is called nesting functions, and in the case of`IF`

's, it's a great way to deal with 3 or more conditional outcomes. Try replacing the last argument of your`IF`

statement with`IF(D1 = 5, D1, D1 - 2)`

so that you end up with`IF(D1 < 5, D1+3, IF(D1 = 5, D1, D1 - 2))`

. When you change the value of`D1`

from 4 to 5 to 6 (and if you get an error, double-check that both statements are closed with parentheses - you should have 2 at the end of the formula).#### Point of Information: Syntax in Google Spreadsheets

If the syntax in the box above is confusing to you, don't worry! There are two ways that we can learn more information about the built-in expressions and functions like`IF`

in spreadsheets:- When you begin typing the “=If” in the formula bar, you will see a box pop up below the bar showing something like
`IF(logical_test, [value_if_true], [value_if_false])`

. This is the program's way of letting you know what values it is expecting within the parentheses following the`IF`

expression. In this case, the first thing we enter after the`=IF(`

, also called an*argument*or a*parameter*, is the logical test`D1 > 10`

, which evaluates to be either true or false. The second parameter, as described above, tells the spreadsheet what to place in cell`D2`

if the logical test is true (the value in cell`D1`

is greater than 10). The third parameter tells the spreadsheet what to place in cell`D2`

if the logical test is false (the value in cell`D1`

is less than 10). So, if the value in cell`D1`

is 4, we can see that the logical test evaluates to false, and so the value in cell`D2`

will be set to 0. - To learn even more information about a function or expression that you wish to use, you can click on the expression that pops up below the formula bar as you are typing, in this case the
`IF`

. This will bring you to a help page on this function, which also happens to be online as well. This information is helpful to read if you aren't sure what each parameter means, and often includes examples of the function's use. Here's a list of the many functions that you may later use in your adventures. Try looking up the information page for the`ISBLANK`

function described in Part 2 above.

- When you begin typing the “=If” in the formula bar, you will see a box pop up below the bar showing something like
#### Point of Information: Capitalization in Google Spreadsheets

When we are writing expressions and formulas in spreadsheets, the capitalization most often doesn't matter. This means that you may see the

`IF`

expression written as`If`

,`if`

, or even`iF`

, and they all are equivalent and will accomplish the same thing (although either of the first two is standard and recommended). It is important to note that in most other programming languages, including Python (which we will see in the second half of the course), capitalization is critical to the meaning of the code, unlike in spreadsheets.

- Once you have completed the tutorials and above exercises, make a copy of
`HW1-1`

from the website and store it in your Google Drive. - Rename your file to something like
`ChanTaiMan_12345678d_HW1-1`

(i.e., your name, followed by your studentID, followed by “HW1-1.”). - This spreadsheet file will contain several exercises. Complete the six exercises in the
`SheetBasicsPart1`

. - You will hand in this file by sharing with the course gmail account at the end of this homework.

Throughout this course, we'll tend to work with very organized data. It'll be very common to have lots of instances that are very similar, which we can organize in columns. For example, suppose we have records for a class: each week we have a quiz or two, and each student gets a grade. (Equivalently, we have a Legislative Council, and every so often we have a vote; each legislator casts a vote.) Our data might look like this (for a very small class):

Name | Week | QuizScore |
---|---|---|

Amy | 1 | 80 |

Mary | 1 | 72 |

Robin | 1 | 90 |

Amy | 2 | 85 |

Robin | 2 | 91 |

Mary | 2 | 65 |

Mary | 2 | 79 |

Robin | 2 | 90 |

Amy | 2 | 70 |

Mary | 3 | 90 |

Robin | 3 | 80 |

Amy | 3 | 79 |

We've entered the grades into our spreadsheet in the order the papers happened to fall during grading, so some weeks Amy is first, other weeks Mary is first, etc. Notice that in week two, we had two quizzes rather than just one.

This spreadsheet is a kind of database, in the sense that it's a bunch of records, where each record provides information about a single name/week/quiz score. The key thing about these records is that each consists of the same three things: a name, a week, and a quiz score. These three things are called fields in the database. (We'll see databases quite a lot more later in the course, so this is just a nice easy example to get you used to some terminology.)

Now imagine that we'd like a summary of each student's average performance, by week. We want a table that looks like this:

Name | Week1 | Week2 | Week3 |
---|---|---|---|

Amy | 80 | 77.5 | 79 |

Mary | ... | ... | ... |

Robin | ... | ... | ... |

To do this, we can create what's called a “*pivot table*,” and Google Spreadsheets has a tool that can produce this for us.

- Read through this tutorial: “Creating Pivot Tables”. Once you've done that, go the the sheet

`SheetBasicsPart2`

. It has 4 more tasks listed in it. Go ahead and do Task 1, which asks you to create a pivot table using the students' weekly average.
There's one tricky thing about pivot tables: they only work for summarizing numbers. For example, if you have a table of letter grades (A, B, C, etc.), then things like “max” and “min” and “average” won't make sense, and a pivot table will be of no use to you. You can still create one, however, with a little trick: suppose you replaced each `A`

with a `4`

, each `B`

with a `3`

, and so on, converting each letter grade to a number on a GPA-like scale. Then you could compute the maximum grade, or the minimum. The “average” would not make sense. You could compute it, but it would have no real meaning. (Why not?)

When you have your pivot table displaying the maximum grades, however, you'd have a table full of 4s, 3s, 2s, etc. That's not ideal. So what can we do? We can take the table and replace all of the 4s with As, 3s with Bs, and so on, converting each GPA number back into a corresponding letter grade. Here is a summary of what we have just done:

- Convert the letter grades to their numerical representations.
- Make a pivot table based on these numerical representations.
- Convert the pivot table results back to the corresponding letter grades.

There's one case where this convert-pivot-convert-back approach is especially effective: when each entry in the pivot table corresponds to exactly one row in the input table. For instance, in class we'll look at legislators' votes on various bills. Each senator gets exactly one vote (Yes/No/Abstain) on each bill. In this case, the operation that is done on each cell (i.e., “max”, “min”, or “average”) will always end up computing that single vote.

You have already seen the addition of the values of two cells in speadsheets, but sometimes you would like to combine the contents of two cells in other ways. If you have `= A1 & " " & A2` into ### Task 12:

`foo`

in cell `A1`

and `42`

in cell `A2`

, you can make `foo 42`

in cell `A3`

by typing `A3`

. The `&`

operation combines two strings of characters by concatenating them.
On `SheetBasicsPart2`

of `HW1-1`

in cell `E26`

, make the contents read `hello, world!`

as a combination of `E23`

and `E24`

, spaces included (Task 2 on spreadsheet).

`If`

Statements**(Independent)**

`IF`

statements are lovely, but so far we've only shown you how to display two different results with them. That is, you can have an "if true" result, and an "if false" result. But what happens if we want more than two results? Let's start looking into this by entering this formula into cell F31:At the moment, this formula only differentiates the numeric equivalent of A (5) from all other grades, returning either 5 for an "A", and "Not A" for everything else. How can we change this to display more grades? The answer lies in adding more=IF(E31 = "A", 5, "Not A")

`IF`

statements**inside**this formula.**Note:**The quotation marks around the`A`

and`Not A`

in the formula above are necessary. The quotations marks tell the speadsheet that the letter is to be treated explicitly as text, and*not*interpreted as a number or a name.-
Change cell

`F31`

so that it matches the following formula:Play around with the contents of=If(E31 = "A", 5, If(E31 = "B", 4, "Not A or B"))

`E31`

until you understand what this formula does, then read the information about nesting functions below. #### Point of Information: Nesting Functions

In the formula above, the third parameter of the first`IF`

expression is another`IF`

expression! This is something that you will see often when writing speadsheet formulas, and is referred to as “*nesting*” functions or expressions. In the formula given in Part 1 above, this means that the spreadsheet will first do the logical test to see whether`E31 = "A"`

, placing a`5`

in the cell`F31`

if the test evaluates to be true. However, if the test evaluates to be false, then it will compute the value of the expression given by the third parameter, which means that it will compute the result of the logical test to see whether`E31 = "B"`

. If this is true, then cell`F31`

will contain a`4`

, and, finally, if this is false then cell`4`

will contain`"Not A or B"`

.Now it's your turn. Change the contents of cell

`F31`

so that it properly converts every grade to its numeric equivalent from cell`E31`

(so A->5, B->4, C->3, D->2, F->1). Also, have it display "Not a grade" if anything else is entered. If this feels challenging, don't worry! This is a tricky concept that can take some time to understand. Try breaking this problem into smaller parts by adding one new grade at a time. Start by changing "Less than 4" into a new`IF`

that displays 3 and "Not A, B, or C".**Hint:**To properly convert the five different letter grades to their numerical equivalents, you will need to write a formula that contains five nested`IF`

expressions. When nesting expressions that are enclosed in parentheses, be very careful to make sure that all of your opening parentheses (`(`

) are matched with a closing parenthesis (`)`

).

- Make sure that you've renamed your spreadsheet file to
`name_studentID_HW1-1`

(e.g. ChanTaiMan_12345678d_HW1-1). - Make sure all tasks are completed (there are two sheets in the file!).
- You will share this file with our gmail account at the end of this assignment.

Congratulations, you're done with your first assignment! Share the HW1 file with `PolyUcomp1d04@gmail.com`

(actually the capitals don't matter, but it makes it easier to read). Make sure you name your spreadsheet properly, or else you won't get the credit!

`Name_studentID_HW1-1`