Excel: Working With Formulas

1
Excel Working With Formulas

• Cooperative Conversations
• January 12, 2007

2
Introduction to Excel

• Workbook, Sheet, Row, Column, Cell

3
Introduction to Excel

• Name Box (Cell Address)
• Formula Bar (Contents of Cell)
• Cells can contain Values or Formulas
• Commas separate values (1,2,3) is 1 and 2 and 3
• Colons indicate ranges (A2A5) is Cell A2 through
  A5

4
Introduction to Formulas

• All Formulas begin with
• Basic Formulas (numbers, cells, or both)

Add Subtract - Multiply Divide /
105 10-5 105 10/5
B3C3 B3-C3 B3C3 B3/C3
B35 B3-5 B35 B3/5
5
Introduction to Formulas

• All Formulas begin with
• Advanced Formulas (multiple operations)
• Math rules apply, multiplication and division are
  done first, addition and subtraction next
• Use Parentheses to manipulate order

Formula Result Formula Result
10-5-2 3 10/52 4
10-52 0 (10-5)2 10
B2-C2-D2 3 B2/C2D2 4
B2-C2D2 0 (B2-C2)D2 10
6
Introduction to Formulas

• All Formulas begin with
• Automatic Functions (Toolbar button S)
• Sum(1,2) or Sum(A3,A7,A9) or Sum(A1A5)
• Average(1,3,5) or Average(A1A3,A5)
• Count(A1A5)
• Max(B2B25)
• Min(B2B25)
• Menu Insert/Function for more options
• Use the Help menu

7
Introduction to Formulas

• Formulas can be combined
• The SUM of participants from the first six months
  (29) PLUS the SUM of participants from the last
  six months (34) is (63) then MULTIPLIED by the
  result of twelve MINUS four (8) to result in 504
• Or the result of a formulacan be used in a
  formula

8
Introduction to Formulas

• Formulas can reference different Workbooks or
  Sheets
• A10Sheet2!B5 is the value from Cell A10 of our
  current worksheet multiplied by the value of Cell
  B5 from Sheet 2
• Formulas can round (ROUND), give results based on
  criteria that you set (IF), or can calculate a
  payment schedule (PMT) based on a principal value
  and interest rates

9
Tips

• Sum (S) toolbar button (etc.) will automatically
  select adjacent cells until interrupted by text
  or an empty cell
• Different cells can be selected by dragging the
  mouse from the start cell to the end cell

10
Tips

• Parentheses will bold briefly when closed to help
  identify which portion of your formula has been
  completed
• Cells will be outlined in the same color as the
  text in the formula when the cursor is in the
  formula bar

11
Tips

• Formulas with errors will have a green triangle
  on the cell, and a symbol on the right when the
  cell is selected that may offer help resolving
  the issue
• Value! Indicates that there is an error with
  the formula in this case there is a number
  being multiplied by a cell that contains text
• Ref! Indicates that there is an invalid cell
  reference usually because a formula has been
  copied or moved
• Indicates that the value to be displayed
  is larger than will fit in the cell