Formulas and Functions Guide

Do's and Don'ts for Creating Formulas and Functions
  • All formulas begin with an equal sign (=).  This distinguishes a formula from values or text.
    • For Example: =A1+B1
  • Parentheses are used to enclose a cell address or cell range
    • Example: (A1:A3) includes cells A1, A2, and A3.
  • A colon is used to identify a cell range - not a hyphen.
  • No spaces are allowed in formulas or functions.
  • All cell references used in formulas are relative by default.
Using Operators for Creating Algebraic Formulas
  •   The structure for an algebraic formula in Excel is:
    • Equal sign, cell reference, mathematical operator, then the next cell reference
      • For Example: =A1*A2.  This formula takes the cell value in A1 and multiplies it by the cell value in A2.
    • Mathematical Operators in Excel include
           +        Addition
           -         Subtraction
           *         Multiplication
           /          Division
  • You can use multiple cell references and mathematical operators within one formula
    • For Example: =A1*A2+B4-C5/D6
    • When using multiple operators in one formula you must use the Order of Operations
Order of Operations in Excel Functions
  • Use the following saying to remember the order in which operations will be performed in Excel:
    • Please Excuse My Dear Aunt Sally
  • Parentheses, Exponents, Multiplication, Division, Addition, Subtraction
    • For Example:  In the formula =A1+A3*A5...
      A3 is multiplied by A5 first.  Then A1 is added to the product of A3*A5.
  • Use parentheses to change the pecking order in formulas; you can even nest parentheses.
    • =(A1+A3)*A5...
      A1 is added to A3 first.  Then the sum is multiplied by A5.
Functions and Their Structure
  • Functions use the name of what action or calculation will be performed instead of mathematical operators
  • The structure of a functions is: Equal sign, name of function, argument.
    • For Example:  =Average(A1:A5)
  •  The name of the function indicates what function will be performed, such as SUM, which means addition will take place.
  • The argument refers to the range of data used in the function.
  • Simple functions include: SUM, AVERAGE, COUNT, MIN, MAX, STDEV, and many, many more!!!
  • The AutoSum Function (Greek Sigma) is used to automatically insert a SUM function in a cell.
  • Other automatic functions can be performed by clicking the downward pointing arrow next to the Sigma.
Subscribe to: Posts (Atom)