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!

Task 1:

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.

Task 2:

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.

Task 3:

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.

Task 4:

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:

Task 5:

  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

When you build a spreadsheet, you're doing so with some intent. You should record your intentions by adding comments to your spreadsheet(s).

Task 6:

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.

Task 7:

  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.

Task 8:

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:

Task 9:

  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.

Task 10:

  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.

Task 11:

  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.

Task 12:

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

Task 13:

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

Task 14:

  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

Task 15:

  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

Task 16:

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

Task 17:

  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.

Task 18:

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

Task 19:

  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

Task 20:

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
“YourName” should be replaced with your first and last name. Make sure every task has been completed.