Document Sample
Arun_Day5 Powered By Docstoc
					Day 5:Excel Chapter 2
 • Homework # 1
    – Due by 11:59 pm on 9/17/2010
    – Follow the instruction at page of our class
    – Please work on your own. Academic Integrity
      is an very serious issue in Cs101
 • Myitlab Lesson #1(Excel Chapter 1-4) due by
   tonight 11.59pm
 • Functions & Cell references
   – Cell References example
   – VLOOKUP Function
 • Worksheet Appearance
   – Commenting, Background Fills, Font Colors, Format
     Painter and Hyperlink
 • Payment Function (PMT)
Basic Statistical functions
      Function            Purpose

      Sum            To find sum of range of numbers

      Average        To find average of range of
      Median         To Mid point value in a range

      Maximum        To find largest number in a range
                     of numbers
      Minimum        To find smallest number in a
                     range of numbers
IF Function & Logical Operators
   =IF(logical_test, value_if_true, value_if_false)
  If logical_test is true, value_if_true is returned.
Otherwise, value_if_false is returned. The return value can
either be a mathematical operation or text display
  Example of valid logical_tests:
   • “Equal”              A1=B1
   • “Greater Than”       A1>B1
   • “Less Than”          A1<B1
   • “Greater or Equal”   A1>=B1
   • “Less or Equal”      A1<=B1
   • “Not Equal”          A1<>B1
Using IF function
 • Example: we want to show under hazardous
   – “yes” if the reading is above100
   – “No” if below (or equal to)100
 • In cell C2 type
                     =IF(B2>B19, “Yes”, “No”)
 • Autofill cell C3 to C11.
   – But we’ve got something unexpected . Why??
Concept of Cell Reference

• Check the formulas from cell C3 to cell C11.
  – It refers to cell B20, B21,… but we have nothing in there!
  – When the formula was copied, the references B2 (and
    B20) were automatically changed to B2, B3, … (and B20,
    B21, …) so that the original relative position between B2
    and B19 is kept. This is called a relative reference.
• To always reference to a specific row (and/or
  column), add $ mark before column/row heading
  (absolute reference)
              =IF(B2>$B$19, “Yes”, “No”)
Cell References
 • When a formula is copied to other cell, the references
   in the formula are automatically changed according to
   its destination. If you don’t want this to happen, you
   need to add a $ mark.
 • Column Absolute:         $B19
    – When it’s copied, column (B) cannot change but row (19)
 • Row Absolute:            B$10
    – When it’s copied, column can change but row cannot
 • Full Absolute:           $B$19
    – When it’s copied, Nothing can change
Date Functions
 • Efficiently handle time-consuming procedures
 • Help analyze data related to the passing of time
 • TODAY function places the current date in the
   selected cell
    – Updates when file is opened again
 • NOW function displays current date and time,
   side by side
 • Used to gauge an entered value against a range of
   corresponding values
 • Used to convert numeric to letter grade..and has many
   more applications
 =Vlookup(Cell to evaluate/Look up_value, Table range
 • Cell to evaluate: cell that has value being Examined
 • Table range: Cell range that contains the look up value
 • Column#: This indicate the column number in the table
   from which result /matching value is to be returned
Example_3 Grade conversion
                          Grade             Points
                     A                    900 OR MORE
                     B                    800-899
                     C                    700-799
                     D                    600-699
                     F                    599 or Fewer

O              F

600            D

700            C         From Syllabus

800            B         Entered in a distant area in Excel Sheet
900            A
example3_Grade conversion
 • Download Example3_Grade conversion.xlsx
   from the class website
 • Auto fit the Column widths first
 • In B2 enter the formula
             =VLOOKUP(A2, A11:B15, 2)
 • Try to enter different numbers in A2 and
   observe how cell B2 changes.
Worksheet Appearance
 • In B2, right click and select “Insert Comment”
   and type “My grade in the class”
 • Select A1:B1
   – Change Font Color to White
   – Change Fill Color to Black
 • Copy the same settings of A1:B1 to A10
   – Click in A1, then select Format Painter (tiny brush
     icon), and click in A10
 • Go to cell D1
 • Insert tab
 • Hyperlink
   – Text to display: CS101 Site
   – Address:
 • OK
 • Try to click on the cell D1 once and see if the
   web browser jumps to the specified link.
Payment Function - PMT
 • Calculates the payment to be paid per month
   at fixed amount and fixed constant interest
 • Use it to determine if it’s something you can
   afford (a car, a house, a boat, . . .)
 • Syntax
      =PMT(interest/12, payments, -financed)
  Example_4(PMT function)
• Download Example_4.xlsx from the class
• Enter the following:
  – B1 (House price)     1,00,000
  – B2 (down payment) 5,000
  – B3 (financed amount) =B1-B2
  – B4 (% interest)      0.06
  – B5 (years)           30
  – B6 (number of payments) =B5*12
PMT function
 • In B7 (Monthly Bill) type
               =PMT(B4/12, B6, -B3)

 • You should see $569.57 a month for 360 months
 • Now change period from 30 years to 15 years:
   – You will see the monthly payment change by 232$
30 years vs. 15 years
 • 30-year model
   569.57*360 = $205,045.2
 • 15-year model
   801.28*180= $144,299.58
 • So you’re saving 60,745.68when you
   go for the 15-year model.
Goal Seek
 • Allows for a “what if” analysis scenario
 • Allows setting a target number and can
   manipulate another number to temporarily see
   what its value should be to reach that specific
 • So now let’s go back to our example and do the
    Let’s say that we want to get our monthly
   payment down to $675 and we need to know
   how much to put down in order to clear the
   loan in the same number of years…
Goal Seek
• Make cell B7 the active cell by clicking in it
• Select “Data” ribbon  Data Tools group 
  What-If Analysis  Goal Seek

• Change the “To Value” 675
• “By Changing Cell:” B2 (it will be absolute
• hit OK
• Observe the new down payment in B2
Next class
 • Continue with Microsoft excel chapter 3

Shared By: