1 / 20

Spreadsheets

What we’ll cover for this lecture topic: The paper worksheet ... What are electronic spreadsheets? Cells cell references cell contents Formulas and functions What are spreadsheets good for? Relative and absolute cell referencing (critical!)

inoke
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. What we’ll cover for this lecture topic: The paper worksheet ... What are electronic spreadsheets? Cells cell references cell contents Formulas and functions What are spreadsheets good for? Relative and absolute cell referencing (critical!) Demos interspersed as time permits Spreadsheets

  2. The paper worksheet The electronic spreadsheet • Worksheets were used in many businesses for inventory management andaccounting. • Key feature: columns to separate different types of information (‘fields’). • Because columns sizes varied depending on the use, many formats were available. Accounting Ledgers (Items, debits, credits, tax info…) Inventory (Items, #in stock, supplier, wholesale $, retail $...) Payroll (Name, SSN, job title, mailing address, salary…) BOTTOM LINE: Lots of paperwork and calculation! Mid 70’s: Dan Bricklin & Bob Frankston--the story. 2

  3. • The electronic spreadsheet • “The most brilliant software invention of the microcomputer era … “ Neill Graham Basically, it’s just a 2-dimensional table: • Each entry occupies a cell... It can hold: • numeric values • alphanumerictext (character strings). • As labels; what about as data? • anentireformula.The real power!

  4. Remember Charles Babbage? Manual spreadsheets had lots of problems… time-consuming to produce difficult to edit and update miscalculation errors transcription errors (recording wrong number) With electronic spreadsheets… calculations are automated (fast, reliable, & simple to modify!) formatting can also be easily changed

  5. In most electronic spreadsheets, columns are indexed by letters, and rows are indexed by numbers. A cell is identified and referenced by writing its column letter(s) followed by its row number. E.g., cell D3 is highlighted in the above picture. Referencing a Cell A B C D E 1 2 3 4

  6. To refer to a block of contiguous cells, specify a starting cell and ending cell separated by a colon. E.g., the marked region below is C2:E4. Non-contiguous blocks of cells can be referenced using a list in which the references are separated by commas, e.g., the reference C2:E4,A1:A3 is the marked block above along with cells A1, A2 &A3. Referencing a group ofCells A B C D E 1 2 3 4

  7. When a cell value CHANGES…. all cell functions that use that cell’s value are automatically recalculated. Immensely USEFUL! • So what’s it good for? • Personal/professional budgets and budget scenarios • Building space allocation • Grades • Income statements, balance sheets • Investment analyses • Keeping track of nearly anything ...

  8. Great for “what-if” analyses: • What if…. • we decrease costs of good sold by 5%, what will our net earnings be? • I earn an 80% on Exam 2, what will my average be? • If my pay gets raised 5%, how much extra take-home pay will I get? Can I afford another loan? Demo 1 & 2….. Meet Microsoft Excel

  9. Excel uses a very simple programming language: • Basic Verb Set (2-operand operations) includes: + – * / ^ Arithmeticoperators: = < <= > >= < > Comparisonoperators: $ , : ( ) And Or Not Special symbols: Booleanoperators: and: • Functions! More about formulas • Nouns? cell references (names) Digression…really interested in? cell contents

  10. Many more operations available as functions. Functions take a collection of cell references as input, e.g.., SUM(A1:A5,B7,C8:C10) calculates the sum of the values cells A1:A5, B7, and C8:C10. A few particularly useful functions: SUM: computes sum of all values PRODUCT: computes product of all values MAX: finds maximum value MIN: finds minimum value Functions

  11. AVERAGE: finds average value COUNT: finds number of non-empty cells in the group that contain numerical data. COUNTA: finds number of non-empty cells in group. IF(condition,A,B): conditional formula. If condition is true, use formula A. Otherwise, use formula B. • Fairly simple syntax for formulas. • Mathematical precedence rules apply (use parentheses)

  12. Each is like a little computer program that lives in a cell. • Formulas can depend upon other cells (which themselves might be the result of other formulas...). Powerful! = SUM(D4:D12) = AVERAGE(A2:A200) Notice the colon:

  13. How does Excel look at a cellreference in a formula? A B C 1 2 3 4 15 30 10 20 3 2 Relative cell references Relativeto where formularesides! Entry bar: B4 = (B1 + B2) * B3 [Enter] Excel interprets the formula in cell B4this way: Find value stored 3 rows upfromformula cell, Add that to value stored 2 rows upfromformula cell, = (B1 + B2) * B3 Multiply that to value stored 1 row upfromformula cell, Display answer. 13 Note: formulas displayed.

  14. What if I copy A4 to C5? What will C5 look like? Row & cell references will change! SO WHAT?Why should you care? Because: --You will want to RE-USE formulas you create as OFTEN as possible! (Copy and Fill) --You must know HOW Excel will INTERPRET your request! A B C D 2 4 6 20 30 40 5 10 15 1 2 3 4 5 3 5 7 = A1 + B2 = C + D 2 3 14

  15. What about here? Very same idea A B C D What if I copy C4 to A6? What will A6 look like? 2 4 6 20 30 40 5 10 15 1 2 3 4 5 6 3 5 7 = C1 +C2 +C3 =A +A +A 3 4 5 15

  16. What about here? A B C D What if I copy C4 to C5? What will C5 look like? 2 4 6 20 30 40 5 10 15 1 2 3 4 5 3 5 7 Did we move columns? = C1 +C2 +C3 =C +C +C 2 3 4 But we DID move ROWS! 16

  17. When you COPY a formula from one cell to another, any RELATIVE references will follow: That is: CELL REFERENCES will CHANGE in the target cell, as just seen. Placing the special symbol$ in front of the row or column identifier in a reference prevents Excel from changing that part of the reference when copying. ABSOLUTE Important Tricks of the Trade

  18. What if I copy A4 to B5? What will B5 look like? 1st: Excel interprets the formula this way: A B C D 10 4 6 20 30 40 5 10 15 1 2 3 4 5 6 3 5 7 Findvalue stored in exactly CELLA1, Subtract from that: value stored in cell: 2 rows up and 1 col to the right; = $A$1 - B2 + C3 $1 3 4 Add that to value in cell 1 row up and 2 cols to the right. =$A -C +D Display answer. 18

  19. Cell references are classified according to the absence or presence of the $ symbol. All three types are used depending on the problem(we’ll look at real examples later that show WHEN/WHY to use different references!) Relativereference: Neither the row nor column identifier is preceded by $. E.g., B4. (the default) Mixedreference: Either the row or column identifier is preceded by $, but not both. E.g., $B4 and B$4. Absolutereference: Both the row and column identifiers are preceded by $. E.g., $B$4. Referencing cells in formulas

  20. When you move a formula, Excel automatically updates the cell references contained in formulas. Examples coming… Nifty Feature: When you edit a formula, Excel will put color-coded boxes around the different cell references. Works great when trying to figure out what is wrong with a formula. Important Tricks of the Trade, con’t DEMO 3

More Related