Excel Chapter 2

• This chapter contains the real meat and potatoes of our excel discussions.
• We will learn the basics of computing in excel.
• The first skill we need to develop is how to copy formulas around.
  • If we enter a computation only once, this is unnecessary
  • But if we have a bunch of the same computations to perform we have two choices
    • Type in each function, one at a time.
    • Develop a generic function and copy it around.
  • Let's compute the square of the first 100 numbers.
    • In Cell A1 put N
    • In Cell B1 put N²
    • In cell A2 put 1, A3 2, A4 3
    • Highlight these and drag down to produce the numbers 1-30
    • In B2 enter =A1*A1
    • Drag the formula down, what does it do?
    • In cell C1 put 3
    • In Cell C2 enter =power(A2,C1)
    • Copy this down in the C column
      • Is that right? (Always ask this, what is 3³?)
      • What went wrong?
        • Look at the formulas, noticed a2 became a3 became a4 which is what we wanted.
        • But C1 became c2 became c3, which we did not want.
      • A2 and C1 are relative references
        • When they are copied down, the number changes by the number of rows they are copied
        • When they are copied across, the letter changes.
      • We can change this behavior by adding a $ in the middle of the cell address.
        Change the value in cell C2 to be =power(A2, C$1)
        • And copy this down.
          • Look at the formulas? What happened?
        • Add 4 5 6 7 to cells D1 trough G1
        • Copy C2 across D2:G2
          • Did it change the way we wanted it to?
          • No, we are no longer square A2, but B2, C2 ...
          • In this case, we don't want the letter to change
          Change the value in cell C2 to be =power($A2, C$1)
          • And copy this down and across.
      • There are three basic types of cell references.
        • relative reference contains no $, changes when copied
        • absolute reference has a $ in front of the row and column, never changes.
        • mixed reference has one $, only part changes.
      • You select the reference type you need for the formula you are using.
    • Formulas are equations that you type in.
    • Functions are prebuilt formulas that are implemented in excel.
      • Monthly payment m on a principle of P with an annual interest rate of r for t years is.
      • m = P(r/12)/(1-(1+r/12)^-nt)
      • The function is = pmt(r/12, 12*t,-P)
      • Let us experiment
        • Grab a new worksheet
        • in A1 put Amount
        • in B1 put 100000
        • in A2 put Rate
        • in B2 put 5%
        • in C1 put 30
        • This represents a loan of $100,000 at 5% apr for 30 years.
        • Now let us compute the monthly payment two ways.
        • In cell A6 enter formula
        • In cell A7 enter function
        • In B6 enter the formula, my formula was =B1*(B2/12)/(1-POWER((1+B2/12),-(12*B3)))
        • In cell B7 enter =PMT(B2/12,B3*12,-B1)
      • Which is better, easier, and less prone to error?
    • In the previous example we isolated our assumptions or built an input area
      • We put the terms of the loan in a single location so that it would be easy to change them.
      • We did not place the values directly into the formulas/functions
      • This makes it easier to change the worksheet if the conditions change.
      • This is a good thing.
    • Ranges
      • If you wish to address a block of cells, you can use a range.
      • A1:A5 refers to the cells A1, A2, A3, A4 and A5.
      • A1:C3 refers to the cells A1, A2, A3, B1, B2, B3, C1, C2, C3
    • Some basic functions
      • =sum(range) add all of the values in the range.
      • =sum(v1, v2, ...) add all of the values in the range.
      • =average(range) find the mean
      • =median(range) find the median
      • =max(range) maximum value in a range
      • =min(range) minimum value in a range.
      • =count(range) count the numeric cells in a range
      • =counta(range) count the cells in a range with a value
      • =countblank(range) count the blank cells in a range
      • =today()
    • Some math functions
      • =abs(number) compute the absolute value.
      • =int(number) turn the number into an integer, truncate
      • =round(number, digits) round number to digits decimal places.
    • For the next concepts I will use this file.
    • vlookup
      • This function allows you to lookup a value in a table.
      • The first column of the table must be sorted
      • The first column of the table contains the value you are searching form.
      • The next columns contain information related to the value in the first column.
      • =vlookup(value, table, column)
        • value is the value to find in the first column
        • table is the range that contains the table
        • column is the column from which you want the return value.
      • In our example, we want to find the cost associated with the membership type.
        • For cell C3, =vlookup
          • The value we are looking up is the type (b3)
          • The table is b10:c12
          • We want the Cost column back (2)
        • =vlookup(b3, b10:c12,2)
        • But we want to copy this down, so make b10 b$10, we could use $b$10, but we will only copy down so ...
        • To the same for C12
        • Copy the formula down
        • Check things out.
      • There is also a hlookup for tables that go the other way.
    • If
      • The if statement allows to ask (and answer) yes-no questions.
      • =if (question, yes action, no action)
      • In our example, we want to find the price per locker.
        • If lockers < 3, the cost is $10, otherwise the cost is $8
        • Form a yes no question
          • Really the only comparisons we can do are on page 474
            • =, equal
            • <>, not equal
            • <, less than
            • <=, less than or equal
            • >, greater than
            • >=, greater than or equal
          • d3 < 3
          • What value do we want if this is true? (b$14)
          • What value do we want if this is false? (b$15)
          • =if(d3< 3, b$14, b$15)
      • We can also decide the price for the Bonus Club
        • =if(h3="Y",c$17,0)
        • Look at the table on page 475 for more examples.
    • Let's do a "safe" amortization table.