Functions and Formulas

• I will use this document
• A formula is a computation in excel
  • It could involve the basic mathematical operations (+,-,*,/,...)
  • It could involve functions (next section)
  • It could involve other things.
  • It also involves cell references and data values.
• Absolute, relative and mixed references.
  • By default, in excel, cell references change when the formula is copied.
  • This is a good thing, normally.
  • But causes a problem when referring to a constant value.
  • To help when copying formulas we have two/three modes.
  • A cell reference with no $ will just copy. (This is a relative reference.
  • A cell reference with two $ will stay the same. This is an absolute reference.
  • A cell with one $ will keep one part the same, and allow the other part to change. This is a mixed reference.
• A circular reference is a formula that directly, or indirectly references it's own value.
  • This is bad.
• Functions
  • Functions are predefined computations.
  • They take parameters or values used in the computation.
  • Excel provides guidance in using functions, but you still need to know how to use them.
  • I would suggest keeping a list of functions we have used
    • What they do
    • What parameters they take.
    • How to use them.
  • I may ask you to read the documentation and use a new function as well.
  • Some functions you should pick up.
    • Any mentioned in a section.
    • Any I use.
  • For example, today
    • sum
    • min, max, median, average
    • count, countblank, counta
    • today, now, date
• We will delay if and pmt until next class.
• But vlookup and hlookup
  • looks for a value in a table.
  • First parameter: the value
  • Second parameter: the table
  • Third parameter, the column
  • Warning, the table must be in order.