1 / 30

Spreadsheets +5 Swords of Calculation

INFO100 and CSE100. Fluency with Information Technology. Spreadsheets +5 Swords of Calculation. Katherine Deibel. Spreadsheets. Powerful tools for managing data Organization Manipulation Calculation Visualization High-level tool for programming

clio
Download Presentation

Spreadsheets +5 Swords of Calculation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. INFO100 and CSE100 Fluency with Information Technology Spreadsheets+5 Swords of Calculation Katherine Deibel Katherine Deibel, Fluency in Information Technology

  2. Spreadsheets • Powerful tools for managing data • Organization • Manipulation • Calculation • Visualization • High-level tool for programming • Think of spreadsheets as a handier interface for computing ideas than JavaScript is Katherine Deibel, Fluency in Information Technology

  3. Familiar Terminology cell row name referenced cell L2 formula column name column heading Katherine Deibel, Fluency in Information Technology

  4. Similar Metaphors to JavaScript • Variables:Cells are variables (contain data) • Expressions:Setting a cell to a formula is an (unevaluated) assignment statement • Functions and Parameters:Spreadsheets have built-in function • Arrays and IDs:The position (row/column) names the data • Iteration:Operating on all elements in a range as in =SUM(A1:A5) Katherine Deibel, Fluency in Information Technology

  5. An Array of Cells • A spreadsheet is a 2-D array of cells • Multiple sheets make it 3-D • The idea is that the rows or columns represent a common kind of data • Adding more data of the same type means adding more rows or columns • Often spreadsheets contain numbers, but text-only spreadsheets are useful, too Katherine Deibel, Fluency in Information Technology

  6. Clicker Quiz • Let's see what your knowledge of spreadsheet basics is like Katherine Deibel, Fluency in Information Technology

  7. When the $ sign is used in a cell reference, it makes that part of the reference absolute. Katherine Deibel, Fluency in Information Technology

  8. Cell references default to the current sheet. Katherine Deibel, Fluency in Information Technology

  9. Spreadsheets use localization information to determine stylings for things like currency. In Britain, currency is formatted as £3.33. Katherine Deibel, Fluency in Information Technology

  10. Spreadsheets are clever in how they calculate functions in that they trace dependencies and calculate in the proper order. Of course, this means that circular references are bad. Katherine Deibel, Fluency in Information Technology

  11. Circular Reference Most spreadsheet programs will warn you against circular references and will not get caught in an infinite operation. Katherine Deibel, Fluency in Information Technology

  12. Spreadsheets by default show the result of a formula in the row. You have to click on the cell to see the formula in the edit bar. Katherine Deibel, Fluency in Information Technology

  13. A cell cannot contain which of the following? • JavaScript code • Russian text • A URL • Date and time • Can contain all of the above Excel cells can contain almost any type of data, JavaScript and Russian included. What it does with the data, however, may not be what you expect. JavaScript code would be treated as just text. Katherine Deibel, Fluency in Information Technology

  14. Power Use of Excel May apply to other spreadsheet programs Consult your physician before stopping power use of Excel Offer not valid in Alaska Katherine Deibel, Fluency in Information Technology

  15. Formulas • The data in a spreadsheet can be manipulated using formulas • The value in H2 (selected cell) is the value in F2 times 0.621 … the result is shown, but the cell has the formula Katherine Deibel, Fluency in Information Technology

  16. Apply Formula Again • One way to repeat the formula is to copy-and-paste Katherine Deibel, Fluency in Information Technology

  17. A Powerful Translation • The relative and absolute references work this way because they are in effect: =[myposition.col -2,myposition.row]*0.621 Katherine Deibel, Fluency in Information Technology

  18. Relative vsAbsolute Addresses • Reference to cells happens in 2 ways: Relative and Absolute (with $) • F2 relative column, relative row • F$2 relative column, absolute row • $F2 absolute column, relative row • $F$2 absolute column, absolute row • Relative references change when pasted/filled • Absolute references do not change Katherine Deibel, Fluency in Information Technology

  19. An Example • Creating a discount table uses rel and abs refs • Consider store credit of $1 per $10 spent • $3 store credit for every 2 CDs (1 earns $1) =$B3/10 + (3/2)*C$2 =[B,myposition.row]/10 + (3/2)*[myposition.col,2] B 2 Katherine Deibel, Fluency in Information Technology

  20. Filling Replicates Formulas • Fill is a spreadsheet shortcut for copy-and-paste • Grab the fill tab with the cursor and pull in the direction to be pasted fill tab Katherine Deibel, Fluency in Information Technology

  21. Series • Another handy property of fill is that it can make a series based on constants • Fill Sunday => Monday, Tuesday, Wed… • Fill 22 Feb => 23 Feb, 24 Feb, 25 Feb, … • More generally • Series fill will even count using a constant • Counting by odd sizes: give 1st two items Katherine Deibel, Fluency in Information Technology

  22. More Advanced Skills • Chapter 15 Case Study – Travel Costs • This chapter guides you through several other power features of Excel • Conditional Formatting • Filtering Katherine Deibel, Fluency in Information Technology

  23. Professor Kate's Advanced Skills Calculations, Formulas, Charts, Etc. Mommy plays with Excel instead of me… Katherine Deibel, Fluency in Information Technology

  24. Formulas • Spreadsheets come with built-in functions for many purposes: • Basic math functions (ABS, FLOOR, EXP) • Statistics (AVERAGE, MEDIAN) • String manipulation (FIND, LEN • Logic (IF, AND, NOT) • And many more Katherine Deibel, Fluency in Information Technology

  25. Formula Help • When you enter a = into the edit bar, you can click on the function button • This brings up a list of functions for you to browse through • Each function also has help files and demos associated with it Katherine Deibel, Fluency in Information Technology

  26. Formula Help • Interactive windows help you fill in formula parameters Katherine Deibel, Fluency in Information Technology

  27. Charts • Visualizations help with communicating large amounts of data • Lots of room for flexibility and creativity • Entire course in itself • Good place to blaze away and explore • Plenty of good tutorials online • One of my favorites is: http://peltiertech.com/ Katherine Deibel, Fluency in Information Technology

  28. As A Workspace • Spreadsheets are great because they have lots of cells • Spread out your calculations • As they said in elementary school math… SHOW YOUR WORK!! Katherine Deibel, Fluency in Information Technology

  29. Example • Let's calculate something complex • Yes, Excel has a built-in function for this (STDEV), but let's do this • By the way, μis the average of the N x's Standard Deviation Katherine Deibel, Fluency in Information Technology

  30. Summary • Spreadsheets are powerful tools • One of the best tools for the digerati • Easy access to programming • Spreadsheets are often the easiest way to make a computer solve your problem if the software for that problem doesn’t already exist Katherine Deibel, Fluency in Information Technology

More Related