# Exploring Microsoft OFFICE Excel Range by MikeJenny

VIEWS: 8 PAGES: 33

• pg 1
```									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,
…)
   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
 (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.

Notice the quote
marks

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