200 likes | 269 Views
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.
E N D
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
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
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
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
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
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
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
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
Formatting a two-digit date CS 105 Spring 2009
Format cell as a number, not a date Date calculations Formula view: CS 105 Spring 2009
Operators in Formulas • Arithmetic operations (in order of precedence) • parentheses ( ) • negation (as in -1) - • exponentiation ^ • multiplication, division * / • addition, subtraction + - CS 105 Spring 2010
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
Functions in Formulas • =TODAY() • =MONTH( March 7, 2006) • =MONTH(A1) CS 105 Spring 2009
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
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
When you cut and paste a cell that is referenced in a formula, then again the formula changes CS 105 Spring 2010
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
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
Copying/Pasting FormulasMixed Cell References What happened when these formulas were copied? CS 105 Spring 2010
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