Exploring Microsoft OFFICE Excel Range by MikeJenny

VIEWS: 8 PAGES: 33

More Info
									Exploring Microsoft OFFICE
        Excel 2003
 Some Common Functions
(Some are one pages 185-186)

           CSCE 120
        Sections 1 and 3
          Spring 2007
                               1
         Common Functions
 Excel provides numerous functions
 We will look at some mathematical and
  statistical functions
 There are about 60 mathematical and trig
  functions
 There are about 80 statistical functions
 There are lots of other functions as well


                                              2
Common mathematical functions
   PI()                Trig functions (SIN,
   RAND()               COS, TAN, ATAN,
   ROUND(value)         DEGREES, RADIANS,
                         …)
   ROUNDDOWN(value)
                        EXP
   ROUNDUP(value)
                        Logarithms (LN, LOG,
   SQRT(value)          LOG10)
   SUM(range)          MOD
   SUMIF(range)

                                                3
    Common “Statistical” Functions
   AVERAGE(range)         LARGE(range)
   COUNT(range)           MAX(range)
   COUNTA(range)          MEDIAN(range)
   COUNTBLANK(range)      MIN(range)
   COUNTIF(range)         SMALL(range)




                                            4
       Comment about “(range)”
 (range)
  is short hand for
  (range, range, …)
 Examples:
     SUM(B10: E15)
     AVERAGE(A1 : C5, E14 : E20, F10 : K10, M1)




                                                   5
             SUM(range)
 Sums the numeric values in a given range
 Ignores blanks and strings




                                             6
        Insert Function command
    I may refer to this as the “Function Wizard”
Click to get
the Insert
Function
Dialog box

Type
function
name or
select a
category                                       7
    Insert Function Command (cont)
 See
  explanation
 Select range
 Get more
  information




                                     8
               SQRT(value)
   Find the square root of some value




                                         9
            COUNT(range)
 Counts the numeric values in a given range
 Ignores blanks and strings




                                          10
          AVERAGE(range)
 Averages the numeric values in a given
  range
 Ignores blanks and strings




                                           11
                   Observations

   Observe that
    AVERAGE(range) = SUM(range)/COUNT(range)
 Questions: Do you see a pattern about how
  blanks and strings are handled?
 Questions: Generally, which would be better?
    =   SUM(A10 : C10)   or    =    A10 + B10 + C10
    =   COUNT(A10 : C10)      or    3
    =   AVERAGE(A10 : C10)     or    = (A10 + B10 + C10)/3
    =   AVERAGE(A10 : C10)     or    = SUM(A10:C10)/3
                                                        12
              MAX(range)
 Finds the largest numeric value in a given
  range
 Ignores blanks and strings




                                               13
              MIN(range)
 Finds the smallest numeric value in a
  given range
 Ignores blanks and strings




                                          14
         LARGE(range, position)
       and SMALL(range, position)
   What if we want the second largest or
    third smallest numbers in the range?




                                            15
         LARGE(range, position)
       and SMALL(range, position)
   What if we want the second largest or
    third smallest numbers in the range?




                                            16
           MEDIAN(range)
 Find the “middle value” of the list.
 Ignores blanks and strings
 If there are an even number of values,
  average the two middle values.

                                   The values after
                                   being sorted




                                                 17
                   Counting
   Perhaps we want to count differently:
     Count all nonempty cells
     Count all blank cells
     Count according to some criteria




                                            18
           COUNTA(range)
 Counts all the data items in the range
 Only ignores blank cells




                                           19
          COUNTBLANK(range)
   Count all the blank cells in a range




                                           20
      COUNTIF(range, criteria)
 Counts the cells that satisfy some criteria.
 See also: SUMIF(range, criteria)


                                 Notice the quote
                                 marks

                                 Adding the
                                 criteria is easier in
                                 the Insert
                                 Function dialog 21
                      PI()
   Gives the value of PI




                             22
      ROUND(value, decimalPlaces)
     Rounds value off to the specified number
      of decimal places
=A1
Formatted
to 2
decimal
places.
Value
unchanged
.
                          =10 * B1    =10 * C1   23
 ROUNDUP(value, decimalPlaces)
ROUNDDOWN(value, decimalPlaces)
   These functions are like ROUND but
    always round up or down.




                                         24
                   RAND( )
 Calculates a random number between 0
  and 1
 Gets a different value each time it is
  calculated                    All 5 cells contain
 Neat for random games         =RAND( )




                                                  25
             PMT function
 Joe wants to buy a big screen TV for
  $2000. If he borrows the money at 12%
  APR and repays it in 5 equal payments,
  how much will each payment be?
 Before answering this question, we need
  to understand some terms.



                                            26
                  APR
 APR: Annual Percentage rate. This
  method is specified by federal law.
 In the case that interest is compounded
  more than once a year
  APR = number of periods per year
           * rate for each period
 Example: Interest is compounded at 1%
  per month (12 times a year)
   APR = 12 * 1% = 12%
                                            27
Simple versus Compound Interest
 Simple interest: Interest is calculated
  using the initial balance
 Compound interest: Interest is calculated
  using the current balance
 Assuming equal rates, which is better for
  the consumer?



                                              28
       Example: Simple Interest
   Mary borrows $2000 at 12% annual simple
    interest and pays $100 each month.




                                              29
    Example: Compound Interest
   Fran borrows $2000 at 12% APR compounded
    monthly and pays $100 a month




                                               30
     PMT(rate, number payments,
           beginning balance)
 Rate is the rate per period
 (If needed), number payments
            = years times payments per year
 The beginning balance is negative !!??!!??
 Excel financial function represent money
  flows in two directions:
      bank ------- borrower
      bank ------- borrower
 One directions is positive, one is negative
                                            31
      Example: PMT function
 Joe borrows $2000 at 12% APR
  compounded monthly for 5 months. What
  is his monthly payment?
 Monthly interest rate = 12%/12 = 1%
 Number of payments = 5
 Beginning balance = -$2000
 Monthly payment = PMT(1%, 5, -2000)
  (but we would use cell references!!!)
                                      32
Example: PMT function




                        33

								
To top