Schedule

1. Schedule • Wrap up Chapter 13 • Homework – Fill out your classmates forms • Next Week: • Chapter 14 • Lab 2 – working with the data from our forms • Test 1 – Ch 1,2,3,9,13,14

3. Learning Objectives • Explain relative and absolute references • Apply concepts of relative and absolute references when filling a formula • Explain the concept of tab-delimited input and output

4. Transforming Formulas: Relative Versus Absolute • The spreadsheet software automatically transforms the formulas as it pastes/fills them into a cell • The cell contains a relativecell reference (F2) • Spreadsheets allow two kinds of cell references—relative and absolute • The absolutecellreference to this cell is $F$2

5. Relative Versus Absolute • Relative means “relative position from a cell” • In the formula H2 =F2*0.621 into H2, the software noticed that cell F2 is two cells to the left of H2 • The formula refers to a cell in the same row, but two cells to the left • This is a relative reference

6. Relative Versus Absolute

7. Relative Versus Absolute • Absolute references always refer to a fixed position—the software never adjusts it • There are two ways a formula can be relative, making four cases: • F2—column and row are both relative • $F2—absolute column, but relative row • F$2—relative column, but absolute row • $F$2—column and row are both absolute

8. Cell Formats • The scores are somewhat difficult to read because they have too many digits • All spreadsheet software provides control over formatting

9. Excel’s Formatting GUI

10. Cell Formats • This GUI gives us control over: • The types of information in the fields (Category) • The number of decimal digits for the Number category chosen • Setting the “1000s” separators (commas for North America) • The display of negative numbers

11. Functions • Spreadsheet software provides functions for computing common summary operations • totals (sum), averages, maximums (max), and others • To use these functions, give the function name and specify the cell range to be summarized in parentheses after it: =max(J2:J7)

12. Functions • There is a full list of function names under Insert > Insert Function. . . . • A computation value inherits the formatting of the cell • When the function is then dragged across to other columns,it brings its formatting with it

13. Charts • Spreadsheets organize our data and compute new values • It is helpful to see the results graphically when comparing values • Spreadsheet software makes creating charts easy • Select the values to be plotted and then click Chart. . .

14. Charts • There are choices of graph styles: • The software detects that the column has a heading and uses it as a label the point as a key to the right. • Clicking (or right-click) on any part of the graph, gets a pop-up window that offers editing options

15. Daily Spreadsheets • Spreadsheets are convenient, versatile tools that simplify computing • Here are some ways to use spreadsheets to organize: • Track our performance in our exercise program • Set up an expense budget • Keep a list of books and DVDs we’ve lent • Follow our favorite team’s successes • Save records generated while online banking • Address books

16. Solving a Problem of Personal Interest • Scenario: • Time Zone Cheat Sheet… people you want to chat with live in different time zones… it’s not always convenient to chat when you want to because they may be sleeping, working, or studying

17. Time Zone Cheat Sheet

18. Series Fill • There is certain data that are “special:” days, dates, and time. • When the software fills these values, it automatically increments them • Adding 1 to Sunday results in Monday • Adding 1 to January 31 results in February 1 • Adding 1 to 12:00 am produces 1:00 pm • However, if you type Sunday and want Sunday, use Copy/Paste

19. Series Fill • The best way to use series fill is to: • Enter the first two items of the series into adjacent cells • Select the two cells • Pull on the handle to fill either the row or column • The double-cell fill indicates a series where increment between successive items is the difference between them

20. Getting Started… • Begin by placing the headings at the top • Under your name enter midnight • Fill down the column to the end of the day • Next, add the times in for your friends • Grab the fill handle and fill the column up and down • The software assumes that rows above are earlier and rows below are later

22. Finishing Up the Time Zones • Add colors to the cells that refer to “yesterday” and “tomorrow” to remind you of day changes • Any cells that start a column and contain “PM” refer to yesterday • Any cells that end a column and contain “AM” times refer to tomorrow

23. Solving a Problem of Personal Interest • Scenario: • Paying Off a Loan…your uncle has agreed to lend you the money, but he’s charging 5% interest. • To decide how much to borrow, create a spreadsheet of monthly payments required for different amounts borrowed for different times

24. Paying Off a Loan Initial Setup

25. Paying Off a Loan • Among the functions available with spreadsheets is the “payment” (PMT) computation • The GUI is displayed for PMT: • The inputs to the function are: • The monthly interest Rate, which is 1/12th of the annual rate • The number of payments (Nper) is the amount in row 2 for this column • The present value, or the amount of the loan (Pv)

26. PMT GUI

27. Conditional Formatting • Specifying the format of a cell under certain conditions is called conditional formatting • Decide what the conditions are that you want to see

28. Importing Data • Much of the data we are interested in comes from some sources • It has probably already been organized • This other data is called foreign data • Importing previously formatted data into a spreadsheet can be tricky • Spreadsheet software makes it easier if guidelines are followed

29. Tab-Delimited Data • One way spreadsheets can import foreign data is as tab-delimited text • “Text” means ASCII text or files with a .txt extensions • Numbers are represented as individual numeral characters rather than a single binary number

30. Tab-Delimited Data • “Tabdelimited” means that each cell’s entry is delimited or ends with a tab in the file • Each row is delimited with a return • There are other delimiters as well: • Spaces • Commas • Copying and pasting tab-delimited text is a simple way to import foreign data

31. Tab-Delimited Data • Lists with some other form can often be converted into tabdelimited form • Copy the foreign data into a text editor or word processor • Editing it using Search/Replace, using the placeholder technique • The goal is to substitute a tab or other delimiter for a delimiter in the file

32. Tab-Delimited Data • World Wide Web information is already in text form • Some spreadsheet software allows for copying a table from HTML and pasting it into a spreadsheet • If it doesn’t work, try another browser before beginning the task of reformatting the foreign data

33. Guidelines for importing foreign data • When possible, save foreign data as tab-delimited ASCII text in a file with a .txt extension. • When foreign data comes from the Web, select a browser that supports Copy/Paste of tagged tables. • When the foreign data format is messed up, use a text editor with Search/Replace, apply the placeholder technique, and write the revised data with a .txt extension. Import the resulting file.

34. Arranging Columns • Spreadsheets are designed to manipulate rows and columns of information easily • To re-arrange columns: • Insert a new empty column where you want a column moved to • Cut and Paste the column that is to be moved into the empty column

35. Summary • In this chapter we explored the basic ideas of spreadsheets. We learned the following: • Spreadsheets present an array of cells, each of which is capable of storing one data item: a number, a letter sequence, or a formula. • Numbers and text can be formatted so that they display as we prefer—proper font, correct number of digits, and so on.

36. Summary • In this chapter we explored the basic ideas of spreadsheets. We learned the following: • The power of spreadsheets comes from entering formulas that calculate new values based on the values in other cells. • The formula is one side of an equation, which the computer solves for us, preserving the equality whenever the numbers that the formula depends on are changed and displaying the new value in the cell.

37. Summary • In this chapter we explored the basic ideas of spreadsheets. We learned the following: • In addition to performing arithmetic on the cells, we can apply functions to individual items or to whole cell ranges. • Both relative and absolute references to cells are needed depending on the circumstances. • In addition to sorting, there are functions for finding totals, averages, the maximum or minimum, and others.

38. Summary • In this chapter we explored the basic ideas of spreadsheets. We learned the following: • Spreadsheets are a practical tool for routine computing. • It’s easy to teach ourselves more about spreadsheets simply by trying them with courage. • Spreadsheets may be the most useful software for personal computing.