Chapter1 (1).doc

30 Pages
Unlock Document

York University
Electrical Engineering and Computer Science
EECS 1520
Nicole Nivillac

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

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.