160 likes | 295 Views
IS 2101—Spring 2010. Chapter 6 Creating a Worksheet and Charting Data. Numbering Note. In the original version of the text, our chapter 6 is numbered ‘9’ So, the projects are ‘9A’ and ’9B’ The figures are numbered 9.1, 9.2, …. Spreadsheets. Rectangular arrays of data called sheets
E N D
IS 2101—Spring 2010 Chapter 6 Creating a Worksheet and Charting Data
Numbering Note • In the original version of the text, our chapter 6 is numbered ‘9’ • So, the projects are ‘9A’ and ’9B’ • The figures are numbered 9.1, 9.2, … IS 2101/07---Spring 2010
Spreadsheets • Rectangular arrays of data called sheets • A spreadsheet file can contain multiple sheets • Data can be • Numeric constant • Non-numeric constant (aka string data) • Formulas • Formulas usually apply to numbers, but can apply to string data IS 2101/07---Spring 2010
“Killer Ap” • Visicalc • Versions 1979 to 1983 • Lotus 123, Quattro Pro other examples IS 2101/07---Spring 2010
Alternatives • Open Office • Gnu office IS 2101/07---Spring 2010
Anatomy of a Sheet • The data in a spreadsheet is entered into cells • Each cell contains one item of data • Data is arranged in rows and columns • Rows are identified by numbers (starting at 1) • Columns are identified by letters (starting at A) • After Z, columns go to AA, AB, …, BA, BB, … , ZZ • After ZZ, columns go to AAA • 16384 columns in all (strange number?) IS 2101/07---Spring 2010
Excel Interface Components IS 2101/07---Spring 2010
Help • Help options • Contents • Index • Search • Functions IS 2101/07---Spring 2010
Creating a Spreadsheet • Start Excel • This starts a new file • Save As IS 2101/07---Spring 2010
Getting Some Data • For illustration, we’ll analyze the Dow Jones Average over the past month • Historical values for the Dow Jones Average • Yahoo Finance • http://finance.yahoo.com/q/hp?s=^DJI • “Download to Spreadsheet” • This is actually a text file, CSV format • We’ll open it directly into Excel IS 2101/07---Spring 2010
CSV Format • Comma Separated Values • This is a text format, so is very portable • This works well for data, but not for formulas • This is the form one uploads to WebCT-Vista to import grades into the gradebook IS 2101/07---Spring 2010
Numeric Formulas • Numbers • Operators • + - * / ^ (raising to a power) • Cell references • A1, B33, AA4, … • Functions • Many categories IS 2101/07---Spring 2010
Entering Data • String data and numeric data • Dates • Formulas • ‘=‘ IS 2101/07---Spring 2010
Dow Jones Data Analysis: Setup • Create a new spreadsheet • Open the Dow Jones source data • Copy and paste some data into the new spreadsheet IS 2101/07---Spring 2010
Analysis: Formulas • Difference between Close and Open • Copying formulas • Relative and absolute references • Ratio of difference to open • Formatting data • Percentages • Decimal points IS 2101/07---Spring 2010
Analysis: Graphing • Bar chart of closing • Copy data to fresh sheet • Line chart of closing • Start with original data • Modify data sources • Special ‘open-low-high-close’ graph IS 2101/07---Spring 2010