1 / 20

CS 105 Spreadsheet Computations

CS 105 Spreadsheet Computations. What is a cell? What is a range? What is a formula? What is a circular reference? What’s the difference between copying text and copying a formula ?. M. memory clear. memory recall. memory set. memory plus. Mem clear. Mem recall. Mem set.

kalia-briggs
Download Presentation

CS 105 Spreadsheet Computations

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. CS 105 Spreadsheet Computations What is a cell? What is a range? What is a formula? What is a circular reference? What’s the difference between copying text and copying a formula? CS 105 Spring 2010

  2. M memory clear memory recall memory set memory plus Mem clear Mem recall Mem set Mem plus Manipulating Data current • A calculator can store two numbers and do simple manipulations Set M to zero M = 0 Set current to M current = M Set M to current M = current Set M to M + current M = M + current

  3. Manipulating Spreadsheet Data • A workbook contains worksheets(spreadsheets). A spreadsheet stores data organized in cells, and can do complex data manipulation. Name box active cell formula bar fill handle The cell location is referred to as its cell reference. CS 105 Spring 2010

  4. B6:E13 Cell Ranges G2 : G8 Single Column Range An adjacent range is a rectangular block of cells and can be as small as a single cell. column letters A1 Single cell A3 : E3 Single Row Range Read as “B6 to E13” Row numbers CS 105 Spring 2009

  5. Naming a Range • A Range Name is a label that you can affiliate with a range of cells. • The range name can be used in formulas to refer to that range of cells. • This provides a level of documentation to your spreadsheet. • Select the desired range • Right-Click and choose Name a Range… • The drop-down list affiliated with the Name box lists all range names. CS 105 Spring 2009

  6. Cell contents can be either Data or Formulas • Data can be either Numeric or Strings: • numbers (e.g. 25.32, -56,$78, 1.02E-3, 5%, 1/4/2009) • strings (e.g., Total, Angelina Jolie, 1-217-244-0000) • Formulas always begin with an equals = and can contain operators, data, cell references, ranges and functions,e.g. = 1+ A1 • Cells also have formatting attributes CS 105 Spring 2010

  7. Numbers as strings • Some “numbers” are entered as text (strings): • zip codes • phone numbers • social security numbers • Such numbers do not get involved in computation. By default Excel left-justifies strings and right-justifies numbers. CS 105 Spring 2009

  8. Date Entries • Dates are a special kind of number – Try 01/01/1900 • Two-digit year values 00 through 29 are interpreted as the years 2000 through 2029 • Year values 30 through 99 are interpreted as the years 1930 through 1999. CS 105 Spring 2009

  9. Formatting a two-digit date CS 105 Spring 2009

  10. Format cell as a number, not a date Date calculations Formula view: CS 105 Spring 2009

  11. Operators in Formulas • Arithmetic operations (in order of precedence) • parentheses ( ) • negation (as in -1) - • exponentiation ^ • multiplication, division * / • addition, subtraction + - CS 105 Spring 2010

  12. Hierarchy of Arithmetic Operations in Excel • Scan the formula from left to right • Evaluate what is in parentheses first • Operations of highest priority are done first • Evaluate from left to right operations of equal priority. = B1 + B2 * C3 = -C1 ^ A1 = (B3-A1)*A1^2/B2 CS 105 Spring 2009

  13. Functions in Formulas • =TODAY() • =MONTH( March 7, 2006) • =MONTH(A1) CS 105 Spring 2009

  14. Bad Formulas!!! Circular References • When a formula references itself (either directly or indirectly) there is a Circular Reference • When this happens, Excel warns you several times!! • BAD: CS 105 Spring 2010

  15. Example: Sudoku • We want to check if each row is legal • must add up to 45 (but this isn’t enough!!) • Copy formula to the remaining rows (formula changes) CS 105 Spring 2010

  16. When you cut and paste a cell that is referenced in a formula, then again the formula changes CS 105 Spring 2010

  17. Copying/Pasting Formulas • Relative Cell Reference • A cell reference that adjusts automatically when it is copied. • By default, formulas that you enter use relative cell references. • For example: B to D = moved 2 Columns 2 to 5 = moved 3 Rows CS 105 Spring 2010

  18. Mixed Cell References • A cell reference that refers to an exact location on the worksheet and does not adjust when copied. • Cell reference is preceded by a dollar sign. • For example: • =($B$5+$C7)*D$9 Absolutely column c Absolutely row 9 Absolutely column b and absolutely row 5 CS 105 Spring 2010

  19. Copying/Pasting FormulasMixed Cell References What happened when these formulas were copied? CS 105 Spring 2010

  20. To Summarize: • What is a cell? What is a range? • What is a formula? • What is a circular reference? • What’s the difference between copying text and copying a formula? CS 105 Spring 2010

More Related