# Homework 1-1

Due February 5, 2013, 2:25 pm

## 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. IMPORTANT: Please use a computer that runs Windows (either your own or one in the CIT) to complete this assignment.

## Basics

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 in Excel. 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 function (press the F1 key!) in Excel to learn how to do things.

One good reference is this Excel 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, as there are quite a few tasks to work through, 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 Excel, feel free to skim over most of the homework, focusing mainly on anything that you are not familiar with. (We strongly recommend that you also try out a few of the exercises with Excel 2010 if you have never used that particular version before, as it does things differently than do previous versions.) There is also a required handin at the end of the assignment that will test most of the subjects covered in this tutorial.

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 work through "Excel Basics" Lessons 1-5. When you're done, you should know how to move around in an Excel 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 Excel, but for now we're doing something very simple.

1. First enter your name in cell `A1`. Then click on cell `B1`, and in the formula bar, type
`=A1`
and hit Enter. The equals sign is essential; it tells Excel 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
`A1`
(notice that there's no equals-sign). Press Enter. Cell `C1` should display the text “A1”.
2. 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.
3. 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`, Excel would see the equals sign and interpret it as a formula. What can you do? This is a special case in Excel: you can type a single quote, and then “=A1”, i.e., you enter
`'=A1`
and 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.

At the lower-left of the spreadsheet, you'll notice some tabs labelled “Sheet1”, “Sheet2”, “Sheet3”, and so on. When Excel starts up, you're looking at `Sheet1`, and the others are hidden. Enter your name in cell `A1` (it may still be there from Task 2). Now click the `Sheet2` tab; 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. Describe what you see in cell `A1` after each click.

Important note: The name “Sheet1” has no blank between the “Sheet” and the “1”; the same goes for all the other sheets.

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. In general, 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`
and press Enter. Your name should show up in cell `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 Excel has interpreted it as a number.

## Naming

“The forms of things unknown, the poet's pen / Turns them to shapes, and gives to airy nothing / A local habitation and a name.” (Shakespeare, Midsummer Night's Dream V.i.15-17). See also Naming of Parts, by Henry Reed..

Naming things is one of the ways that we impose structure on the world. While Excel 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:

1. Click on cell `A2`. Just to the left of the formula bar, you'll see a white space with “A2” in it. Click on this and type age and then press Enter. The cell is now named “age” in addition to “A2”.
2. Click on `B2`, and enter the formula =age; the result should be that your age appears in cell `B2`.
3. 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”).
4. 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”.

#### Point of Information: Renaming Sheets

Click on the `Sheet3` tab. Then right-click on it. 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”. To learn a bit more about things that you can do with worksheets, check out Lesson 7 in the tutorial.

## Descriptions

Right click on cell `A2` of `Sheet1`. Select `Insert comment`. Type Enter your age in this cell as a comment. Notice:

1. A small red triangle appears in the upper right of the cell to indicate there's a comment for the cell.
2. When you hover your mouse cursor over the cell, the comment appears.

## Filling

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.

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. There are many ways to accomplish this, such as by selecting some text, right-clicking on it, and choosing `Copy` from the menu that pops up. Review the tutorial for how these operations work in Excel 2010.

#### A Useful Side Note: Keyboard Shortcuts

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. 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 Excel: by filling.

1. Review the next portion of the tutorial on using “Fill Handles.”
2. There are other ways to perform filling on the cells besides those described in the tutorial. First, select all of the empty cells you would like to fill, as well as the cell(s) you are going to “copy”. In the ribbon under the `Home` tab and the `Editing` group, there is a `Fill` button that looks like a blue arrow pointing downwards.

Point of Information: The button might not actually have the word “Fill” on it, and this is also true of many of the buttons along the ribbon. The icons can tend to be overwhelming if you don't know what they mean, but there is an easy way to discover this information. Just hover your mouse over a button on the ribbon, and a description of what it is used for will pop up.

When you click on the `Fill` button, it should give you a menu for different options. Pick `Series`. This should give you a dialog providing different options to use to fill the selected cells. Play around with these options to get a feel for what they do.

Work through the tutorial's “Lesson 6: 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: when you tell a friend how to get to your home, you might say something like “Take route 42 for the first 8 miles. If it's rush hour, take 302 to go around the city; if not, just take route 42 straight through the city. On the other side, whichever route you took, watch for signs for Millville, and take the first Millville exit...” 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 Excel, 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:

1. In cell `D1`, enter `12`. In cell `D2`, enter the formula

`=If(D1 > 10, 3, 0)`
Observe the result. Change the value of `D1` to `4`, and observe the result.

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

`=If(D1 < 5, D1+3, D1-2)`
gives `D1+3` if `D1` is less than five, but if it's five or more, the expression gives `D1-2`.

3. #### Point of Information: Syntax in Excel

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 Excel:
• 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 Excel'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 Excel 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 Excel 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 Excel's 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 functions' use. Here's a list of the many functions that you may later use in your Excel adventures. Try looking up the information page for the `ISBLANK` function described in Part 2 above.
4. #### Point of Information: Capitalization in Excel

When we are writing expressions and formulas in Excel, 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 and the opposite is true than what we see in Excel.

1. Once you have completed the tutorials and above exercises, download `HW1-1Part1.xlsx` from the website.
2. Rename your Excel file to something like `JadrianMilesHW1-1Part1.xlsx` (i.e., your name, followed by “HW1-1Part1.xlsx”).
3. This Excel file will contain several exercises. Complete these exercises and save your finished spreadsheet.
4. You will e-mail this file to the TAs at the end of this homework.

## Pivot Tables

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 senate, and every so often we have a vote; each senator casts a vote. Or we're running Netflix, and every so often a customer rents a movie.) Our data might look like this (for a very small class):

NameWeekQuizScore
Amy180
Mary172
Robin190
Amy285
Robin291
Mary265
Mary279
Robin290
Amy270
Mary390
Robin380
Amy379

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:

NameWeek1Week2Week3
Amy8077.579
Mary.........
Robin.........

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

1. Work through the tutorial's “Lesson 20: Creating Pivot Tables”.
2. Download `HW1-1Part2.xlsx`, and do Task 1 in the spreadsheet, which asks you to create a pivot table using the students' weekly average.

## A Note About Pivot Tables

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, by a little subterfuge: suppose you replaced each `A` with a `5`, each `B` with a `4`, 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 5s, 4s, 3s, etc. That's not ideal. So what can we do? We can take the table and replace all of the 5s with As, 4s 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:

1. Convert the letter grades to their numerical representations.
2. Make a pivot table based on these numerical representations.
3. Convert the pivot table results back to the correspondingletter 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 senators' votes on various bills. Each senator gets exactly one vote (Yea/Nay/Not Voting) 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.

## Concatenating Two Cells

You have already seen the addition of the values of two cells in Excel, but sometimes you would like to combine the contents of two cells in other ways. If you have `foo` in cell `A1` and `42` in cell `A2`, you can make `foo 42` in cell `A3` by typing = A1 & " " & A2 into `A3`. The `&` operation combines two strings of characters by concatenating them.

On `Sheet1` of `HW1-1Part2.xlsx` in cell `E26`, make the contents read `hello, world!` as a combination of `E23` and `E24`, spaces included (Task 2 on spreadsheet).

## More `If` Statements

(Independent)

1. In cell `F31`, enter a formula that produces a 5 if `E31` has an `A` in it, a 4 if `E31` has a `B` in it, and so on. Hint: try out this formula to see what it does:

`=If(E31 = "A", 5, If(E31 = "B", 4, 700))`
Change the formula so that it works for all letter grades (A, B, C, D, and F), and fill in the column labeled “Numerical Representation” with your formula. If you need a hint as to how to do this, read through Part 3 on “Nesting Functions” below.

Note: The quotation marks around the `A` and `B` in the formula above are necessary. The quotations marks tell Excel that the letter is to be treated explicitly as text, and not interpreted as a number or a name.

2. If the value in cell `A1` is not a letter grade, what should you put in cell `A2`? A good idea is to put something that will stand out, like `Bad Data`. Do so.
3. #### Point of Information: Nesting Functions

Note that 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 Excel formulas, and is referred to as “nesting” functions or expressions. In the formula given in Part 1 above, this means that Excel 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 Excel 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 `700`.

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

1. Rename your Excel file to something like `JadrianMilesHW1-1Part2.xlsx` (i.e., your name, followed by “HW1-1Part2.xlsx”).
2. Make sure all 4 tasks are completed.
3. You will e-mail this file to the TAs at the end of this assignment.

## Stock Report

1. Create a new spreadsheet called `StockReport`. Use `Data > Get External Data > From Web` to import the “Most Recent Activity” table 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. Now using `Data > Connections`, change the `Properties` of your connection to “Refresh every 2 minutes”. This will make Excel check the source webpage every two minutes to see if anything in the table has changed, and hopefully you will see the stock price being updated dynamically!
3. Use `Home > Conditional Formatting` to make the background of the “Change” cell be pale green if the price is rising (which means the value in the cell will probably be positive) and pale red if it's falling (which means the value in the cell will probably be negative). Review the tutorial lesson on conditional formatting if you can't remember how to do this.
4. Finally, save your spreadsheet as `YourNameHW1-1Part3.xlsx`.

## The `Row()` Function

1. Open `Sheet2` of `YourNameHW1-1Part3.xlsx` and name it `FillStuff`. Put the number `7` in cell `A1`. Use `Fill > Series` 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 comment for cell `C1`. Now execute the fill, and in a comment 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 comments on row `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 Parts 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 array. We've seen this before; when you used the fill handles in Task 7, you were copying values over a range, or array, of cells.

Many functions in Excel only make sense to use when we are computing some value over an array 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 of cells 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`. Cell arrays can also be a range of cells over a row (`A1:G1`), or over both rows and columns, forming a matrix (`A1:G9`).

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 `E18`. The resulting values are called the partial sums of the numbers in `A17:E17`. 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.

1. Since there are three `Q`s 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` have worked to make the second formula in the previous task? Use `Fill` to fill in formulas for cells `B24:C24`. 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 comment in cell `A24`.
7. Save your file (which includes both the Stock Report and FillStuff) as `YourNameHW1-1Part3.xlsx`, where `YourName` is replaced with your first and last name. You will e-mail this file to the TAs at the end of this assignment.

1. Download `GradeSheet.xlsx` from the website.
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. Save this spreadsheet as `YourNameHW1-1Part4.xlsx`.

## Final Handin

Congratulations, you're done with your first assignment! Send the following files to `cs0931handin@cs.brown.edu` with the subject line “YourNameHW1-1”:
1. `YourNameHW1-1Part1.xlsx`
2. `YourNameHW1-1Part2.xlsx`
3. `YourNameHW1-1Part3.xlsx`
4. `YourNameHW1-1Part4.xlsx`