1 / 18

Spreadsheets

Spreadsheets. 25 February 2014. Why Study Spreadsheets?. Useful tool Computational thinking Will apply back to JavaScript Using EXCEL Concepts same for other spreadsheets Syntax and functions will vary. Why Use Spreadsheets?. Repetitive calculations Frequency Complexity

jaclyn
Download Presentation

Spreadsheets

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. Spreadsheets 25 February 2014

  2. Why Study Spreadsheets? • Useful tool • Computational thinking • Will apply back to JavaScript • Using EXCEL • Concepts same for other spreadsheets • Syntax and functions will vary

  3. Why Use Spreadsheets? • Repetitive calculations • Frequency • Complexity • Different views • Analysis of data • Explorative • Manipulation

  4. Basic StructureSpreadsheet (Worksheet) Column (letters) Row (numbers) workbook = collection of worksheets

  5. What Can Be In a Cell • Label – identification for people • Constant – any format • Text, number, picture, hyperlink, … • Value for computer • Format for people • Formula – uses cells & constants • Always begins with =

  6. Simplest Formula • =cell • Why do you use it? • Fundamental Principle: • Never have to change anything in two places • Copy-paste • Fine if you really want a snapshot • Does not work if data will change

  7. Best Practice • Name spreadsheets (rename) • Delete unused spreadsheets • Separate input, computation, and output • Input: only page that you change • Output: readability • Computation: everything else

  8. Formulas Referencing Cells • Once you define the formula • Can change the values as often as you like • Automatically re-computes • Treats cells as variables • Defined by location, not value • Each cell constant or another formula • Example • Pay = hourly rate * hours worked • Values can change • Formula remains the same

  9. Copying formulas • Want the same information for different data • Example: min, max, avg grades for each assignment • Can use copy or fill • Copying a formula moves it relatively

  10. Dragging (Fill) • Bottom right corner • One cell copies • Value exact • Formula changes cells • Multiple cells extrapolate

  11. What if I Want the SAME Place • Absolute positioning • Can lock the cell, column or row • Cell: $A$1 • Column: $A1 • Row: A$1 • To change a reference to absolute • Insert $ • PC: Use F4 • Mac: Cmd-T

  12. Formulas Using Constants • Use constants when they will not change • Values that won’t change: • Computing the area of a circle • Π r2 • Computing the area of a triangle • ½ base*height • What about… • Minutes in an hour? • Days in the year?

  13. Constants • Directly in formulas Or • In cells • Used frequently • Precision: make sure it’s right! • To name cells or ranges

  14. Name Manager • Can use to name also helpful to find names or delete them • Accessing: • PC Formulas -> Define Name • Mac Insert -> Name -> Define

  15. Useful Keys and Practices • Paste specials • Values • Transpose • With and without formatting • ESC: This key is your friend • Changing cell sizes • Deletions and additions

  16. Looking at Formulas On PC On Mac

  17. In this class • Workbooks will always be linked from web page • Means that is will be uploaded to isis just like a picture or … • Worksheet names for input and output defined

  18. Assignments • Will include • Spreadsheet • Web page • Data • will include TWO input sets for testing • Will be given a third set of data to post • May be tested on a fourth set of data

More Related