Unformatted text preview:

Chapter 14 Fill in the blank Computing The Basics of Spreadsheets Arranging Information Organizing textual information into lists Fluency with Information Technology Third Edition An array of cells Spreadsheets give us cells we fill in to set up our list by Lawrence Snyder Entry that is too long for a cell may spill over in appearance but still only occupies the cell into which it is typed 1 2 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley Sorting the Data Adding More Data to the List Alphabetize or sort especially when the list is long We can format cell entries Italic bold underline font styles sizes justification color Say what items to alphabetize by selecting highlighting the list Found under the Format menu Naming rows and columns Sort operation is found under Data menu Automatic naming scheme columns are labeled with letters rows with numbers Ascending or descending order as strings or numbers We can refer to a whole column column C whole row row 4 or single cell C4 1 3 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 2 14 3 1 4 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 4 Headings In addition to cell addresses it is convenient to name rows and columns meaningfully Example Common Name Genus Species 1 5 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 5 1 6 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 6 Computing with Spreadsheets Most common application is to process numerical data Writing a Formula Begin with sign define the value for the entry based on the value of the other entries Formulas contain numbers cell references and standard arithmetic operations F2 0 621 1 7 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 7 1 8 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 8 Transforming Formulas Relative versus Absolute Computing with Spreadsheets cont d Relative means relative position from a cell Repeating a Formula If we re copying a formula to a cell two columns to the right of the original the formula adjusts all cell references two columns right Copy Paste Replicates equation to other cells Software automatically adjusts references Absolute means unchanging denoted by using in front of the part of the cell address that does not change Filling Small box or tab beyond the cell s lower right corner fill handle Grab with cursor and pull to other cells Automated copy paste C3 C 3 C 3 1 9 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 9 1 10 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley Cell Formats Functions Control over the format of information displayed Give the function name and specify the cell range to be summarized in parentheses Format Cells Number We can control number of decimal places setting of 1000 s separators and how to display negative numbers max J2 J7 finds highest value in range denotes a range Available function names are listed in the fx symbol and in Insert Function menu 1 11 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 1 12 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 12 Filling Hidden Columns If columns are hidden but we have copied formula across all columns formula operates on hidden column also 1 13 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 13 1 14 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 14 Charts Graphical representation of spreadsheet data Select values to be plotted charted then Insert Chart Chart Wizard The Chart Wizard walks us through the graphing process Can see a preview of different graphs with our data 1 15 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 15 1 16 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 16 Daily Spreadsheets Spreadsheets can organize personal information Track exercise performance Set up expense budget Keep lists of books and CD s we ve lent out Follow a team s successes Record flight hours after each flying lesson Document expenses or income Save records generated by online banking 1 17 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 17 1 18 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 18 Calendar To make a custom calendar with spreadsheet software Enter first day of week Sunday and fill across next six columns list of days completes automatically Below Sunday enter date and fill across Enter first two times going down a column on left side format to taste and fill down 1 19 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 19 1 20 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 20 Discount Table Paying Off a Loan Suppose a store offers Suppose you are considering a large purchase 1 00 store credit for each 10 00 spent plus 3 00 store credit for every two CD s purchased one CD earns only one 1 00 credit You have been offered a loan at 5 interest Create a table of the monthly payments required for different amounts borrowed for different times Construct a table to figure your credits Left column is dollars spent in 10 increments Top row is CDs Purchased in 1 CD increments These are the axes of the table Table entries Formulas to calculate the correct credits remembering some references have to be absolute Fill a row across the top with different numbers of payments fill a column with different amounts Get first cell formula correct then fill across and down to fill the table 1 21 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley 14 21 1 22 Copyright 2008 Pearson Education Inc Publishing as Pearson Addison Wesley Paying Off a Loan cont d Importing Data Use the payment function PMT Foreign data data from another application we want to import into a spreadsheet Inputs are Spreadsheets prefer to import foreign data as tabdelimited text Interest RATE Number of payments Nper Present value amount of loan Pv ASCII text files Each cell s entry ends with a tab Each row ends with a carriage return ENTER Spreadsheets can output as tab delimited If list is in some other form Search Replace can often convert to tab delimited Some browsers can automatically re format


View Full Document
Download The Basics of Spreadsheets
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view The Basics of Spreadsheets and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view The Basics of Spreadsheets 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?