Class Notes
(806,820)

Canada
(492,456)

York University
(33,494)

EECS 1520
(163)

Nicole Nivillac
(2)

Lecture

# Chapter1 (1).doc

Unlock Document

York University

Electrical Engineering and Computer Science

EECS 1520

Nicole Nivillac

Fall

Description

COSC1520.03 The Glade
Computer Use: Fundamentals
Laboratory Handbook
Chapter 1:
Introduction to the Microsoft Excel Spreadsheet
Objectives
This laboratory introduces you to the Microsoft Excel spreadsheet. You should gain an
understanding of the following topics:
• The range of uses of a spreadsheet
• The basic elements of an Excel spreadsheet
• Manipulation of an existing spreadsheet
• Use of formulas to calculate values
• How to develop a simple spreadsheet of your own
Laboratory Preparation
In order to complete this lab in 3 hours it is necessary for you to have a clear idea of your
objectives. This lab will introduce a number of different spreadsheet models that you will
modify in some way. In each case you will need to understand certain principles
demonstrated by the models. You will also be led step by step through the development
of a new Excel spreadsheet. It will not be enough for you to just ‘follow the steps’. The
ideas and techniques introduced here are building blocks for future labs and you should
make every effort to understand what you are doing and why you are doing it, rather than
just following the steps.
• Read the whole of this lab carefully
Introduction
This lab has two parts. In the first part you will see some prepared spreadsheet models
which illustrate the fundamental ideas of a spreadsheet and the range of uses to which it
can be put. In the second part you will develop a simple spreadsheet model yourself.
Starting Microsoft Excel
Refer to the section on launching applications in Chapter 0.
A window resembling Figure 1.1 should appear after Excel has launched. You
won’t be using the new spreadsheet that appears but it is worthwhile examining
some of the features of the Excel application before closing this new worksheet.
1-1 The Glade COSC1520.03
Computer Use: Fundamentals
Laboratory Handbook
The most obvious feature is the array of rows and columns that constitute the worksheet.
Notice that the columns are labeled with letters across the top of the worksheet (A
through to K are visible) and that the rows are labeled with numbers down the left-hand
side (1 through to 23 are visible). These column and row labels form a primitive method
of naming each cell on the worksheet – B6 for example is the name of a cell in the B
column six rows down. Such names can be used in formulas, although we’ll see better
ways to name cells later on in this chapter.
Figure 1.1a the Excel 2003 window at launch
1-2 COSC1520.03 The Glade
Computer Use: Fundamentals
Laboratory Handbook
Figure 1.1b the Excel 2007 window at launch
1-3 The Glade COSC1520.03
Computer Use: Fundamentals
Laboratory Handbook
Entering Data into Cells
The second feature you should note is that one of the cells is highlighted with a
black border. You can’t see it in Fig. 1.1 but on your computer screen it should be
cell A1. Point to any other cell with your mouse and click once. This changes the
highlighted cell. Notice that the label in the area just above the worksheet on the
left of the window contains the name of the selected cell:
Type anything you like (your name perhaps) into the cell that you selected – i.e.
just start typing and you will see whatever you type appear in the cell. At the
same time whatever you type will appear across the top of the worksheet – in an
area that is called the formula bar, which looks like this in Excel 2003, and has a
similar look in Excel 2007:
When you have finished typing press the Enter key and observe what happens to
the cells on the worksheet. Do the characters you typed exceed the cell width?
Experiment with entering large and small amounts of text in different cells.
Next select a different cell and this time type a number and press Enter. What do
you observe about the placement of numbers as opposed to small amount of text
in a cell?
Other Worksheets in the Workbook
Next observe at the bottom of the screen that there are tabs labeled Sheet1,
Sheet2 etc. Currently Sheet1 is highlighted but if you now click on one of the
other sheets you will go to another worksheet of cells. This other worksheet is
currently empty. Click on Sheet1 to go back to the one on which you typed
some data. There can be many worksheets in a spreadsheet model, with formulas
linking them together.
Notice too the arrows next to the sheet tabs, which allow you to move forwards or
backwards through the various sheets.
The Toolbar in Excel 2003
Now observe the two rows of icons near the top of the Excel 2003 window:
1-4 COSC1520.03 The Glade
Computer Use: Fundamentals
Laboratory Handbook
Carefully point to one of the icons, but don't click, and wait a second or two. You’ll see
that a few words appear describing the operation that the icon represents. Most of them
probably won't make much sense just yet, but you will use many of them during this
course. These icons provide a quick method of performing certain common operations.
Take a few moments to carefully explore them. Some of the operations performed by
them are duplicated in the menu commands described next.
The Excel Command Menus
The portion of the window shown in the next picture is called the command menu.
Click on the File menu and observe the selection of commands that appear. Do the same
for the other choices, taking care not to actually select any command at this point. Just
familiarize yourself with them.
The Excel 2007 Ribbon Interface
Excel 2007 replaces the Command Menu and Toolbar with a "Ribbon" interface. Click
on a choice on the top line (Home Insert Page Layout etc.), which is similar to the
Excel 2003 Command Menu, and a set of commands appears in the form of a ribbon
below. In Figure 1.1b you see the ribbon for Home.
Closing the Workbook
You don’t want to use the workbook you have been playing around with. This has just
been a brief tour of the Excel application interface. You should now close this workbook
so that you can begin to examine the demonstration models. There are two ways to close
a workbook – you can either choose the Close command from the File menu (in Excel
2007, the Office Button -- circle with golden background at the top left hand corner) or
you can click on the x button in the top right hand corner of the workbook window. If
you chose the latter method be careful to click on the x in the workbook window, which
is the lower set of window controls. Do one of these now.
You’ll find that a dialogue window appears asking if you want to save the workbook -
you should click No:
1-5 The Glade COSC1520.03
Computer Use: Fundamentals
Laboratory Handbook
Exploring Some Spreadsheet Models
Opening an Existing Spreadsheet
There are potentially three ways of opening an existing spreadsheet. One is to use the
Open command from the File menu (Office button in Excel 2007); another, in Excel
2003, is to use the Open icon from the toolbar; and the third, which is only a possibility if
you have previously used the spreadsheet, is to select its name from a list of recently
opened files, also available from the File menu. This time you will have to use one of the
first two possibilities.
An Open dialogue window will appear and you should navigate through the file system to
the folder containing the lab materials. Refer back to Chapter 0 if you have forgotten how
to do this.
To locate the following Demonstration Models, go to the course website and click on the
"Resources" button, then select "Support Files" and then "1". Save the files to your
computer for future use.
Demonstration Model 1
The model you have just opened contains two worksheets. The names have been changed
from Sheet1 and Sheet2 to Comments and Summing; and the Comments worksheet
is the one displayed.
The Comments Worksheet
1-6 COSC1520.03 The Glade
Computer Use: Fundamentals
Laboratory Handbook
This worksheet contains a brief description of the purpose and features of the spreadsheet
model. It illustrates a documentation principle that you should follow in all of the
spreadsheet work you do. Documentation of your work is crucial – not only for other
people who may need to understand or modify it, but also for yourself when you return to
it after a few weeks or months and find that you have forgotten what you did.
The Summing Worksheet
This worksheet is shown in Fig. 1.2. It shows three methods of adding a row of numbers.
The first thing you should do is examine the contents of the cells by selecting certain
ones. For example, select the cell B1. It looks like it might have the characters “ a l” –
i.e. part of “a list” in the cell. However, you can see from the formula bar that cell B1
is actually empty. Select cell A1 and you will see the entire first row displayed in the
formula bar.
Figure 1.2 the Summing worksheet in demonstration model 1
1-7 The Glade COSC1520.03
Computer Use: Fundamentals
Laboratory Handbook
Select cell B6 and you will see that it contains the number 6. Select cell H6, however,
and you will see not the number 34 displayed in the formula bar but rather a formula,
which has the value 34 as its result from adding the values in the row. Examine the
formulas that calculate the other sum values and observe their differences.
Changing Values on the Spreadsheet
Select one of the numbers in a row and type a new value, pressing Enter when you’ve
done. Notice that the sum value is instantly changed to reflect the new value. This
interactive feature of a spreadsheet is one of the reasons spreadsheet programs are such
valuable tools in so many problem solving applications.
1-8 COSC1520.03 The Glade
Computer Use: Fundamentals
Laboratory Handbook
Inserting a Column (or Row)
Add a new column to the spreadsheet so that a new number can be included in the row
that is being summed. To do this, select one of the columns C, D, E, F, or G by clicking
on the letter in the top row of the worksheet. Excel 2003: Then from the command menu
click on Insert and select the Columns option. Excel 2007: Then from Home, click on
Insert within Cells. A new empty column should appear to the left of whichever column
you had selected. A row may be inserted in the same manner – that is, by selecting a
whole row by clicking on its number on the far left (you’ll select the row below where
you want the new one to appear) and choosing Insert/Row from the command menus in
Excel 2003, and from Home, click on Insert within Cells for Excel 2007.
The Generality of Formulas
Now select the new cell in one of the rows of numbers and type a new value that you
would like to be included in the sum. Do this for each of the three rows of numbers
observing in which cases the value for the sum also changes. One of the formulas gives
the wrong answer when this new value is inserted, and hence you should draw the lesson
that this type of formula should be avoided. Delete the new column you inserted once you
have done this.
Naming a Range of Cells
Click on the little downward pointing arrow to the right of the name bar (on the left of the
window just above the column headings A, B, C etc:
Thename bar:
You’ll see a single name, Values, appear. Click on that name and observe that the third
row of numbers is highlighted. This row of numbers has been defined to have the name
Values in this model. It is possible to name single cells or groups of cells like this. The
aim is to define easily remembered names for data in your worksheets. These names can
then be used in formulas so that they are easier to read, rather than having only cell
references such as B13 or whatever.
Click on the Sum value for this row and observe that the formula that calculates this
value uses the name Values.
Functions
The formulas for the sum in methods two and three also use a function – the SUM
function. Functions take the general form of
SomeFunctionName( someArgumentList )
You’ll use various functions extensively in later chapters. For now notice that the SUM
function has as its argument list a single value expressed as either a range of cells
(B12:G12 for example) or the single name, list, representing a range of cells. This is the
1-9 The Glade COSC1520.03
Computer Use: Fundamentals
Laboratory Handbook
most common, although not the only way, of using the SUM function. Other functions
might have more than one argument with the arguments separate by commas.
Demonstration Model 2
Close the previous spreadsheet model (you don't need to save it) and open the one called
Demonstration Model 2. Read the Comments worksheet carefully before you switch to
the second worksheet called DataSheet. The DataSheet worksheet is shown in Fig. 1.3.
Figure 1.3 – the DataSheet worksheet from demonstration model 2
This worksheet demonstrates a number of new methods of making calculations, as well
as a few new functions. The raw data on this worksheet – that is the data that is entered
by hand and therefore not calculated by formulas – is contained in the columns labeled X
and Y. Names have been created for these two columns. Click on the arrow in the Name
Box and select first X and then Y to see what they refer to.
1-10 COSC1520.03 The Glade
Computer Use: Fundamentals
Laboratory Handbook
Copying Formulas – the Fill Command
The data in the other columns labeled X+Y and X*Y is created very easily, even though
apparently a lot of formulas are involved. Select the cell D6 containing the value 10 and
you’ll see that it is calculated by the formula B6+C6. The other cells in this column are
essentially calculated using the same formula except that the row numbers change.
Examine a few of them. These formulas are created very easily by first typing the top
formula, then selecting that cell and all of the others in the column (which are empty at
this point) to which you want the formula to apply, and then choosing from the Edit
menu the Fill/Down command in Excel 2003 and from Home and Fill/Down within
Editing in Excel 2007. This command copies the top formula into all of the selected cells,
changing the row numbers as appropriate. The other columns of calculated data are
created in essentially the same way – one formula was typed for the top cell, and then
filled down the column.
Using Named Ranges in a Formula
Examine the formulas for the first X*Y column. The names X and Y refer to the whole
columns (as you observed in a previous section) and here the same formula is filled down
the X*Y column to calculate all of these values. Although the names refer to the whole of
the X and Y columns the formula “knows” to use just the two X and Y values on the same
row in order to calculate any particular X*Y value.
Some New Functions
This worksheet also introduces some additional functions in order to familiarize you with
their use. You’ve seen the SUM function before – and now you can see the MIN, MAX,
AVERAGE and MEDIAN functions, all using a column of data as their argument. The
column is either expressed as a range of cell references or as a named range. Close the
worksheet before you move on to the next example (there's no need to save it).
Demonstration Model 3
Open Demonstration Model 3. This model implements a slightly more complex
formula than the previous examples, and also demonstrates the use of the
graphing capabilities of a spreadsheet.
The model calculates the distance an arrow will travel if fired with an initial velocity and
an initial angle, and assuming the ground is perfectly flat. The initial velocity is treated as
a parameter – that is, it is held constant while the initial angle is varied and the distance is
calculated for each angle.
The model has three worksheets – the Comments worksheet (read it carefully), the
Parameters worksheet (Figure 1.4) and the Trajectory worksheet (Figure 1.5). Besides
1-11 The Glade COSC1520.03
Computer Use: Fundamentals
Laboratory Handbook
the initial velocity the Parameters worksheet also contains the other constant quantity –
the gravitational constant. (Notice that the units are included in a cell adjacent to the
value.)
Figure 1.4 – the Parameters worksheet in the Flight of an Arrow model
The Trajectory worksheet (Figure 1.5) contains a column of values for the
initial angle and a column of values for the calculated distance the arrow travels,
as well as a graph of the relationship.
Nested Functions
The formula to calculate the distance the arrow will travel is:
2
distance = velocity * sine( 2*angle ) / g
Click on one of the values in the Distance column in the Trajectory worksheet and
observe the formula as it is expressed in Excel. In this formula two functions are used –
but one of them is nested within the parentheses of the other. The formula is:
=V*V*SIN(2*angle*PI()/180)/g
Launch Angle is in degrees (you can see this in the worksheet) but the SIN function
expects the angle to be in radians. Therefore, the angle is converted by multiplying by
pi/180. And pi is represented by the second function - PI(). Notice that the function PI()
does not have any arguments, as indicated by the empty parentheses – it simply produces
the value of pi as its result.
Inter-Worksheet Formulas
1-12 COSC1520.03 The Glade
Computer Use: Fundamentals
Laboratory Handbook
The formula that calculates the Distance values in the Trajectory worksheet uses the
named values V, angle, and g. However, V and g refer to values that are in a different
worksheet, the Parameters worksheet, and hence the formula is actually what we will
call an inter-worksheet formula.
Use the Insert/Name/Define command in Excel 2003 to open the Define Name
window and select either V or g from the list of names. In Excel 2007 use
Formulas/Name Manager to see the list of names. You’ll see that the cell reference is
prefixed by the worksheet name – Parameters! in this case. This method of specifying a
cell or range as coming from a particular worksheet is part of the syntax of Excel
formulas. Take careful note of this syntax because you'll meet it frequently and
sometimes you might want to be able to type it out yourself. Close the Define Name
window again.
A Graph
Graphs of many different types can be created by a spreadsheet program – and either
placed next to data on a worksheet (as in this example) or placed on a separate worksheet
of their own. One interesting feature is that the graph is interactive in the sense that it is
redrawn immediately if you change any of the data from which it is derived.
Observe the maximum distance that the arrow can travel (it is about 7.5m for an initial
angle of 45 degrees) and then switch to the Parameters worksheet where you can change
the value of the Initial Velocity. Type a new value and return to the Trajectory
worksheet. You should see that the graph has changed to reflect the new data.
Figure 1.5 – the Trajectory worksheet in the Flight of an Arrow model.
1-13 The Glade COSC1520.03
Computer Use: Fundamentals
Laboratory Handbook
Demonstration Model 4
Open Demonstration Model 4. This model contains 4 worksheets – the Comments
worksheet; a worksheet containing a graph and values for the initial velocity and angle of
a canon ball fired over a hill; a Parameters worksheet containing values which define
the shape of the hill (and the gravitational constant); and a Hill_and_Projectile
worksheet containing the data that is to be graphed. The graph is shown in Figure 1.6.
Figure 1.6 – the Canon Ball Trajectory
The main new feature of this model is the ease with which the model parameters can be
adjusted in order to achieve some objective. The objective in this case might simply be to
have the canon ball hit the far right corner of the land (i.e. where the black line reaches
the edge of the graph). One approach is simply to change the velocity and/or angle
parameters hoping by trial and error to hit the corner. Try this now.
A second, much more powerful method, available only in Excel 2003, is to ask the
spreadsheet itself to adjust a parameter in order to reach the target. You can do this by
clicking on the graph to select it and then clicking on the canon ball trajectory line in the
graph (some blocks appear marking the line as selected). Click once on the right most
block on the trajectory line. The other blocks will disappear and you'll be able to
1-14 COSC1520.03 The Glade
Computer Use: Fundamentals
Laboratory Handbook
carefully drag the far right block to the position you would like it to be and release the
mouse button. The following window appears:
All you need to enter in this window is the By changing cell: text box. In other words
enter whichever parameter you want to change in order to reach the goal. So simply
switch to the Graph worksheet and click on one of the velocity or angle values before
clicking OK. The spreadsheet will compute briefly and then display the new parameter
value and the new graph.
New Functions and Operators
Besides this goal seeking demonstration the model also provides examples of the use of
yet more functions. Examine the formula for the Z column and you’ll see the use of the
x
EXP function (which calculates the value of e , where x is the argument of the

More
Less
Related notes for EECS 1520