Class Notes (806,582)
Canada (492,335)
CS 100 (114)
Dan Brown (12)

Module 2 notes.docx

24 Pages
Unlock Document

University of Waterloo
Computer Science
CS 100
Dan Brown

Module 2 notes 2.0 Introduction to Spreadsheets Programs to manipulate spreadsheets were among the first applications to earn the title “killer app,” since they demonstrated that computers could be used effectively by those who were not expert computer scientists. The first successful spreadsheet program was VisiCalc, invented by Dan Bricklin and Bob Frankston (Harvard Business School) in 1978. Photo from D. J. Power, “A Brief History of Spreadsheets,” VisiCalc provided automation for common paper-based accounting practices, as do all spreadsheet programs that came after it. Therefore accountants and other financial officers were familiar with the user interface and found it natural to enter data and formulas and interpret the output that was presented. Soon others found the simplicity of working with rows and columns of data convenient for many diverse applications, and spreadsheet programs grew in popularity. Today spreadsheet software is a part of every collection of productivity tools. Some examples of spreadsheet software are Excel (from Microsoft), Quattro Pro (from Corel), Lotus 1-2-3 (from IBM), and Calc (from In CS 100, you will use Excel or Calc, both of which can work with .xls files. Throughout these notes, examples will be displayed from Excel, but they would appear essentially identically in any other spreadsheet application. There are many popular books that describe how to use Excel; the sections that explain the workings of the spreadsheet itself can help you understand how to use any spreadsheet software, but the sections that explain how to use the menus and other support functionality are peculiar to the specific software and may not carry forward even within the Excel product line. Therefore the first thing to learn is how to use the help features of the software you are going to use. Whenever you cannot find a particular feature on the system you are using or whenever you need extra examples of some of the spreadsheet functions, you should consult the online Help documentation provided with your system. 2.0.1 Using Excel for the Mac The CS 100 Lab’s Macs all have Excel pre-installed; if you are using your own Mac, you need to install either Excel or Calc. The standard toolbars for Mac’s Excel are shown below. Note the Help menu at the top of the window, as well as the Help button (the yellow icon including a black question mark) on the right side of the standard toolbar. Notice also the formula bar, which is one of the major input areas for Excel. 2.0.2 Using Excel for Windows There are two standard versions of Excel available on PCs. Excel 2003 is part of Office 2003, and uses standard toolbars similar to many other applications on Windows. If your software is more than a couple of years old, you will likely be using Excel 2003. Again notice the Help menu at the top, the Help button at the right of the list of icons beneath it, and the formula bar on the following figure: In an attempt to make menus more natural to users, Microsoft created a new method for presenting menus as part of Office 2007, and therefore Excel 2007 has quite a different look and feel. In place of traditional drop-down menus, each menu behaves like a folder’s tab, opening a display of icons on the so-called Ribbon when it is clicked. There is no Help menu, but there is again a Help button on the right side near the top of the window. The formula bar, however, has not changed. 2.0.3 Using Calc As an alternative to Microsoft’s proprietary software, Sun Microsystems developed an “open source” suite of applications based on the Open Document Format and available from Open Office can be downloaded for no charge as follows: 1. In your browser, open the page at 2. Navigate to Open Office’s download site. 3. Click “Download…” to get the software. 4. Execute the file you download to install the software. The installation provides not only Calc for manipulating spreadsheets, but also applications for working with documents, presentation slides, images, and databases. We will not explore these other applications in CS 100, but you might find them extremely useful outside of this course. Calc uses a menu system much like other spreadsheet applications. Notice again the Help menu, the Help button, and the formula bar. 2.1 Spreadsheet Basics An electronic spreadsheet is similar to a traditional paper spreadsheet, which deals mainly with numeric data (e.g., budgets, taxes, inventory, etc.). An electronic spreadsheet, however, is much more powerful and versatile. For example, an electronic spreadsheet can automatically perform calculations and recalculations when data is updated. The following figure shows a sample worksheet with gridlines, row headings (1, 2, 3, …) and column headings (A, B, C,...). The workspace for a spreadsheet file is called a workbook. Each workbook includes one or more worksheets that are large grids of rows and columns as in the figure above. Each column (vertical) is represented by one or more letters, and each row (horizontal) is represented by a number. The intersection between a row and a column is called a cell. Each cell is identified uniquely by its location within the grid, and this unique combination is called the cell’saddress. An active cell’s address is shown in the Name Box in the Formula Bar. In the above figure, the active cell (outlined in black) is found in column D and row 2 (as indicated in orange); its address is therefore D2, which is displayed in the Name Box. It is often convenient to refer to a rectangular region of cells, known as a cell range. Cell ranges have two addresses separated by a colon. Ranges can span multiple columns and rows (e.g., D4:H8 is highlighted in the figure above), part of one column (A6:A15), part of one row (E11:G11), or a single cell (B3:B3). The range notation is also extended to be able to define a complete column (J:J) or a complete row (17:17), even though these range descriptors do not involve a pair of valid cell addresses. (Be sure that you understand in what way these are not pairs of valid cell addresses.) 2.1.1 Kinds of Cell Entries In a worksheet, every cell can contain a value and can be displayed using a suitable format. There are three kinds of entries that can be placed in a cell: o Labels (e.g., Cost, 12 Arbour St.); o Numeric values (e.g., 34, 22.45); o Formulas (e.g. =B3/A2, =10*B3). A label can contain letters, punctuation, digits, and other characters; it can be formatted using any of various fonts and sizes. (Labels are often called text strings, character strings, or just strings in other applications.) A numeric value can contain a number only, including positive and negative values, with several decimal places. Note that a phone number such as 555-1234, however, is a label because it contains both text (the hyphen representing something other than negation) and digits (5551234). Numeric values can also be used to represent financial information, and these can be displayed using common financial notations. For example, a value in dollars (e.g. $22.50) is a number even though it is displayed with a non-numeric character ($). If such a dollar amount were to be represented in a cell, the value would be the number 22.50 but the cell would be formatted to look like currency. Other common number formats include scientific (e.g. 1.23E+05), date (e.g. 17-May-03), time (e.g. 12:26:53PM), percentage (e.g. 15%), and fraction (e.g. 5/8). Note that if you are using Calc on a Mac, by default two or fewer decimal places will be displayed in all cells containing numeric values. This behaviour differs from Excel and other versions of Calc. To ensure that you will see the same results for your calculations as Excel users do: 1. In the preferences for Calc (menu > Preferences...), select OpenOffice.orgCalc > Calculate. 2. Change the number of decimal places displayed to 10. Unlike numbers and text, when a formula is entered, it must begin with an equals sign. We shall examine formulas in more detail shortly. Practice Exercises 1. Consider the following spreadsheet: 2. What is the value in cell B19? What type of value is this? 3. What is the range of addresses that are enclosed in the box that includes the cell with the label Discounts? 4. What is the range of addresses for the cells that are displayed in a Currency format? 5. How many labels are stored in cells in the range B6:C13? 2.1.2 Entering and Modifying Values To enter a value in a worksheet, click on the cell in which you want the value to appear and then type the entry. The spreadsheet software changes to entry mode as soon as you begin to type. Entry mode is distinguished by the next to the formula box in the formula bar. While in entry mode, clicking on cancels the current changes being made (like Undo) and clicking on completes the cell entry. Another way to complete the cell entry is to press the Enter or Return key. Once the formula is completed (e.g. by clicking the check mark), the worksheet is no longer in entry mode. There are three ways to change to entry mode. To replace the contents of the cell or to add contents to an empty cell, just begin to type the new value: you are switched to entry mode automatically. If, however, you need to modify the current cell’s contents, click in the formula box or double-click on the cell to switch to entry mode. Practice Exercises 1. Download the file Photocopy.xls and open it. 2. Click in cell B16 to make it active. 3. Change the value in cell B16 to be 30. 4. Change the value in cell D10 to make it display $0.20. 5. Make cell A3 active. What value is stored in that cell? What value is stored in cell B3? Notice that the display for labels can extend outside of the cell boundaries. Enter the value 0 into cell B3. Notice the effect on the display of the value in cell A3. 6. Not all cells need to be the same size. Extend the width of column A as follows: Hover your cursor over the line dividing columns A and B at the top of the spreadsheet window so that it displays as a double-headed cursor. Then click and drag to the right to get an appropriate width for viewing all of the data in cell A3. 7. Increase the height of row 11 by similarly dragging down the boundary between row 11 and row 12. 8. Save the file in your CS 100 folder under the name NewPhotocopy.xls. (The menu item you need might be labeled Save As…; if you do not know how to save a file, find the information in the Help documentation for your system.) 9. Decrease the width of column D by dragging its right boundary to the left until it is approximately at the slash in the label cost/sheet. Note that the numeric values are displayed as #### and the label in cell D6 is truncated. Such a sequence of number signs is displayed whenever a cell is too small to show its numeric value, and the label in cell D6 is truncated rather than extending into E6 because its format is set as “Wrap text.” Fix the column widths so that all cells are properly displayed again. 2.2 Formulas The primary function of an electronic spreadsheet system is to create formulas to perform calculations that summarize data. When entering a formula, you must begin with an equals sign to distinguish it from a label or a number. Formulas can contain any combination of values, cell references (see Section 2.2.2), cell names (see Section 2.3) or functions (see Section 2.4). Here are some examples of spreadsheet formulas: = 25.4 + 0.15 adding two numbers dividing two values located in two different = A4 / B3 cells = SUM(A5:A6) using a function to add the values in a range of cells = 12 * Tax_Rate multiplying two values, one of which is located in a named cell 2.2.1 Order of Operations When two operators appear in an expression, which one is done first? For example, is 4+5*2 equal to 18 (the addition before the multiplication) or 14 (the multiplication before the addition)? Spreadsheet formulas follow the same order of operations used with standard mathematical formulas, just like you learned in school: multiplication is always performed before addition, unless brackets group things together to indicate otherwise. The phrase used to remember the order is BEDMAS, which stands for Brackets, Exponents, Division & Multiplication and Addition & Subtraction. Remember that within their order group, the division and subtraction operators are executed left to right. For example, 1-2+3 = (1-2)+3 = 2 is not the same as executing the addition first 1-(2+3) = -4. Similar reasoning is required when we will deal with Boolean expressions for search queries later in the course. 3 In most computing applications, superscripts cannot be used to write an exponent (e.g. 2 to indicate 2*2*2) and so the caret character is used instead (e.g. 2^3). Note that the caret is entered by holding the shift key and typing 6. Practice Exercises 1. What answer would the following spreadsheet formula produce? = (2 * 16)/2^2+16-4 2.2.2 Cell References Much of the power of spreadsheets comes from using formulas that include one or more cell references rather than using numbers only. For example, the formula =5*A3 produces the value equal to five times whatever is currently in cell A3: if A3 contains the value 2, then the formula produces 10, but if the value in A3 is subsequently changed to 3, the formula automatically produces the value 15. Thus cell references in formulas play the same role as variables in conventional mathematical formulas. Just as a mathematical formula can contain several variables, a spreadsheet formula can contain several cell references. Practice Exercises 1. Complete the following table: for each row, show the result of the formulas at the top of each column when the cells referenced have contents as shown in the first three columns. For example, the first formula produces 18 when cells G7, G9, and H7 have the values 15, 6, and 3, respectively; similarly the second formula produces 4.5 when those cells have the values 13.4, 3, and 1.5, respectively. G7 G9 H7 =G7+3 =G9*H7 =(G9-1)*(G9-H7) 15 6 3 18 -20 4 5 13.4 3 1.5 4.5 2. Open the file NewPhotocopy.xls again. In cell E8, enter the formula =D8/C8, which reflects the cost per page for Double-sided copying. You should see the value $.06 displayed in cell E8. Click on that cell and look in the formula bar (above the top of the spreadsheet) to verify that the cell’s content is still actually a formula, not a numeric value. Now change the contents of cell D8 to $0.14 and observe that the content of cell E8changes automatically. 3. Still using NewPhotocopy.xls, copy the contents of cell E8 (click in the cell, and then on a PC enter Ctrl-C by holding down the Ctrl key while pressing C, or on a Mac enter Command-C by holding down the Command key while pressing C). Paste the contents into cell E9 (click in that cell and then enter Ctrl-V on a PC or Command-V on a Mac). Observe the value displayed in that cell; look in the formula bar too. In this last exercise, the formula =D8/C8 from cell E8 was changed to =D9/C9 when it was pasted into cell E9. Notice the pattern. What would happen if E8 were copied again and pasted into cell E10? What if it were pasted into cell D16? Try it to check your hypotheses. A formula such as =D8/C8 is said to use relative addresses, because the cell addresses involved are considered to be relative to the cell in which the formula is initially entered. In cell E8, the address D8 refers to “the cell in the same row and just to the left of this cell,” and the address C8refers to “the cell in the same row and two columns to the left.” Therefore when the formula in cell E8 is copied to cell E9, it retains that same meaning relative now to E9, and when it’s copied to cell D16 it again retains the relative semantics. Practice Exercises 1. Complete the following table by showing what formula would appear in each of the given cells if the formula shown were copied from the first cell shown. For example, if formula 1 were copied from cell G6 to cell H7, then =H3+2 would appear in cell H7. In cell Formula 1 Formula 2 Formula 3 G6 =G2+2 =F6*G1-1 =J10-E5 H7 =H3+2 D10 E8 2. How can you override this “rewriting” behavior of cell addresses if you don’t want the formulas to change when copied from cell to cell? Spreadsheet systems provide three types of cell references: relative (e.g. A5), absolute (e.g. $A$5) or mixed (e.g. A$5 or $A5). The presence of the dollar sign in an address indicates that the following row or column indicator is not to be changed when copying the formula to a different cell. A quick way to insert a relative reference into a formula is to click once on the cell while in entry mode. Other references must be entered manually, or they can be changed from relative to absolute or mixed addresses by using function key F4 in Excel or shift-F4 in Calc (look up “absolute address” in the Help pages for your system to learn more). Practice Exercises 1. Complete the following table by showing what formula would appear in each of the given cells if the formula shown were copied from the first cell shown. In cell Formula 1 Formula 2 Formula 3 G6 =$G$2+2 =$F6*G$1-1 =$J$10-E5 H7 D10 E8 2. Similarly to using Copy and Paste, a formula can be moved from one cell to another using Cut (Ctrl-X on a PC or Command-X on a Mac) and Paste. Note, however, that when a formula is moved rather than copied, it is not rewritten; instead all addresses in the formula remain unchanged in the new cell. Cut, Copy and Paste are common tools that can be found in most applications. 2.2.3 Filling Cell Entries Often it is valuable to be able to copy a formula or value into several cells quickly. Spreadsheet systems provide the Fill operation to enable users to “fill” information into adjacent cells. Information can be filled up or down within the same column or right or left within the same row. In general, to fill values from a cell: 1. Make the source cell active. 2. Click on the fill handle (a small button in the lower right corner of the active cell) and do not release the mouse button. 3. Drag across the row or across the column across all the cells in which you want to fill the same formula. 4. Release the fill handle in the last cell in the row or column in which you wish the formula to appear. Practice Exercises For these exercises, download and open the file Relative and Absolute.xls. 1. Click on cell D4 to make it active. 2. Fill down through the shaded boxes, ending on cell D9. Notice the effect on the formulas that have been copied from cell to cell. 3. Similarly fill up from cell J9 to cell J4. Again notice the effect on the formulas. 4. Finally fill right from E14 to I14. 2.2.4 Formula Errors An error message will appear in a cell if there is a syntax error in the formula stored in that cell. Error Meaning Message #DIV/0! Calculation requires dividing by zero. #N/A No value is available/no value matches the request. #NAME? A name used in the formula does not match those defined (see the next section). #NUM! There is a problem with a number (the one calculated or the one used in the formula). #REF! The formula refers to a cell that is not valid (e.g. has been deleted). #VALUE! You are trying to perform an operation on incompatible types (e.g. adding text and numbers). Of course, just because no error message appears, you cannot conclude that the spreadsheet is correct. A formula might specify E5 when it should have specified F5, E6 or J23; it might specify adding two numbers when it should have multiplied them; and so forth. Unfortunately, there is no way for a spreadsheet to report the existence of a logic error, where you erroneously wrote the wrong expression due to a typo or a “thinko” you must proofread your spreadsheet to determine the validity of each cell’s contents. 2.3 Addressing and Naming Cells Download and open the file Simple Sales.xls. This spreadsheet is intended to record the monthly subscriptions for a film supplier, who charg
More Less

Related notes for CS 100

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.