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.
A1
. Then click on cell B1
, and in the formula bar, type
=A1
B1
should show your name, just as cell A1
does. Now click on cell C1
and in the formula bar, type
A1
C1
should display the text “A1”.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.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
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
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.
“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:
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”.B2
, and enter the formula =age; the result should be that your age appears in cell B2
.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”).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 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.
Right click on cell A2
of Sheet1
. Select Insert comment
. Type Enter your age in this cell as a comment. Notice:
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 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.
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:
In cell D1
, enter 12
. In cell D2
, enter the formula
=If(D1 > 10, 3, 0)
D1
to 4
, and observe the result.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)
D1+3
if D1
is less than five, but if it's five or more, the expression gives D1-2
.IF
in Excel:
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.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.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.
HW1-1Part1.xlsx
from the website.JadrianMilesHW1-1Part1.xlsx
(i.e., your name, followed by “HW1-1Part1.xlsx”).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):
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 Excel has a tool that can produce this for us.
HW1-1Part2.xlsx
, and do Task 1 in the spreadsheet, 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, 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:
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.
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).
If
Statements(Independent)
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))
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.
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.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 ()
).
JadrianMilesHW1-1Part2.xlsx
(i.e., your name, followed by “HW1-1Part2.xlsx”).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.
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!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.YourNameHW1-1Part3.xlsx
.Row()
FunctionSheet2
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.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
.
Column()
; describe its behavior.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).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.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.
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 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
).
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
.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.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.
Q
s in the table, enter the numbers 1
, 2
, and 3
in cells A20:C20
.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.A22:C22
,enter a formula that subtracts one from the corresponding entries in cells A21:C21
.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
.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.=Offset($A15, 0, A$22)
as a comment in cell A24
.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.GradeSheet.xlsx
from the website.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!YourNameHW1-1Part4.xlsx
.Congratulations, you're done with your first assignment! Send the following files to cs0931handin@cs.brown.edu
with the subject line “YourNameHW1-1”:
YourNameHW1-1Part1.xlsx
YourNameHW1-1Part2.xlsx
YourNameHW1-1Part3.xlsx
YourNameHW1-1Part4.xlsx