New Perspectives on Microsoft - PowerPoint PPT Presentation

1 / 34
About This Presentation
Title:

New Perspectives on Microsoft

Description:

Microsoft Excel 2002. Tutorial 2 Working With Formulas and Functions ... New Perspectives on Microsoft. Excel 2002 Tutorial 2. 3. XP. Work with the Insert ... – PowerPoint PPT presentation

Number of Views:103
Avg rating:3.0/5.0
Slides: 35
Provided by: course168
Category:

less

Transcript and Presenter's Notes

Title: New Perspectives on Microsoft


1
Microsoft Excel 2002
  • Tutorial 2 Working With Formulas and Functions

2
Use Excels functions
  • You can easily calculate the sum of a large
    number of cells by using a function.
  • A function is a predefined, or built-in, formula
    for a commonly used calculation.
  • Each Excel function has a name and syntax.
  • The syntax specifies the order in which you must
    enter the different parts of the function and the
    location in which you must insert commas,
    parentheses, and other punctuation
  • Arguments are numbers, text, or cell references
    used by the function to calculate a value
  • Some arguments are optional

3
Work with the Insert Function button
  • Excel supplies more than 350 functions organized
    into 10 categories
  • Database, Date and Time, Engineering, Financial,
    Information, Logical, Lookup, Math, Text and
    Data, and Statistical functions
  • You can use the Insert Function button on the
    Formula bar to select from a list of functions.
  • A series of dialog boxes will assist you in
    filling in the arguments of the function and this
    process also enforces the use of proper syntax.

4
Math and Statistical functions
This chart shows some commonly used math and
statistical functions and a description of what
they do.
5
Define functions, and functions within functions
  • The SUM function is a very commonly used math
    function in Excel.
  • A basic formula example to add up a small number
    of cells is A1A2A3A4, but that method would
    be cumbersome if there were 100 cells to add up.
  • Use Excel's SUM function to total the values in a
    range of cells like this SUM(A1A100).
  • You can also use functions within functions.
    Consider the expression ROUND(AVERAGE(A1A100),1)
    .
  • This expression would first compute the average
    of all the values from cell A1 through A100 and
    then round that result to 1 digit to the right of
    the decimal point

6
Open the Insert Function dialog box
  • To get help from Excel to insert a function,
    first click the cell in which you wish to insert
    the function.
  • Click the Insert Function button. This action
    will open the Insert Function dialog box.
  • If you do not see the Insert Function button, you
    may need to select the appropriate toolbar or add
    the button to an existing toolbar.

7
Examine the Insert Function dialog box
This dialog box appears when you click the Insert
Function button. It can assist you in defining
your function.
8
Insert Function helpful tips
  • In the Insert Function dialog box you can type in
    a description of what you would like to do in the
    Search for a function text box and then click the
    Go button, and Excel will come up with some
    suggestions for you.
  • You may also select a category from the drop-down
    box, or choose All, which will list every
    function in Excel alphabetically.
  • Once you make your selection and click the OK
    button, another dialog box shows you all the
    arguments for the function.
  • The arguments shown in boldfaced type are
    required
  • While the cursor is in an argument's text box,
    you can either enter a value or click a cell and
    that cell will appear in the text box, saving you
    the trouble of typing it

9
Excel's financial functions
  • Financial functions are very useful to calculate
    information about loans.
  • Common functions are FV, IPMT, PMT, PPMT and PV.
  • All these financial functions will use similar
    arguments that differ based upon which function
    you are using.
  • Think of the arguments as members of an equation
  • The arguments represent the values of the
    equation that are known and the function provides
    the solution for a single variable, or unknown,
    value

10
Use the financial functions
  • The FV function calculates the future value of an
    investment based on periodic, constant payments
    and a constant interest rate per period.
  • The IPMT function provides the interest payment
    portion of the overall periodic loan payment.
  • The PMT function calculates the entire periodic
    payment of the loan.
  • The PPMT function calculates just the principal
    payment portion of the overall periodic payment.
  • The PV function calculates the present value of
    an investment.

11
Financial Function descriptions
This chart shows some commonly used financial
functions and a description of what they do.
12
Recognize optional arguments
  • In the preceding figure, note how rate and nper
    are arguments for each function.
  • For some of the functions, the final two
    arguments of each function are in brackets. These
    represent optional arguments, meaning if you do
    not enter anything, the default values for these
    arguments will be used.
  • For example, note the PMT function has fv and
    type as its final two arguments, which are
    optional. The assumed values, if no others are
    supplied, are 0 for both
  • Arguments without brackets do not have default
    values, so you must supply values or cell
    references in order for the function to be able
    to return a value.

13
Use the Insert Function dialog box to enter
function arguments
This figure depicts how you would enter argument
values for the PMT function using the Insert
Function dialog box.
14
Copy and paste formulas and functions
  • Copying and pasting a cell or range of cells is a
    simple, but highly effective means for quickly
    filling out a large worksheet.
  • To copy and paste a cell or range
  • Select the cell or range to be copied and then
    click the Copy button on the standard toolbar
  • Select the cell or range into which you want to
    copy the selection and then click the Paste
    button on the standard toolbar
  • Once you are finished pasting, press the Esc key
    to deselect the selection

15
Copy and paste effects on cell references
  • Copied formulas or functions that have cell
    references are adjusted for the target cell or
    range of cells.
  • For example, if cell G5 contains the formula
    F5B5/B7, and you copy and paste this formula to
    cell G6, the formula in cell G6 will be
    F6B6/B8.
  • This may or may not be correct for your
    worksheet, depending upon what you are trying to
    do.
  • You can control this automatic adjusting of cell
    references through the use of relative and
    absolute references.

16
Using copy and paste to copy formulas
  • A perfect example of using the copy and paste
    formulas is the Payment Schedule section on the
    Mortgage worksheet in the figure on the next
    slide.
  • Note in the Mortgage Worksheet figure that the
    first row of data, functions and formulas is
    filled out. This same format needs to be repeated
    for as many payments as there are.
  • Since you have 240 payments, as shown in cell B8,
    you will need 240 rows of data in the Payment
    Schedule. The same basic math applies for each
    column in the schedule.

17
The Mortgage Worksheet
18
The Mortgage Worksheet
  • You can see that the period number should
    increment by 1 as you go down the rows until you
    reach the 240th payment.
  • The Extra payment column is 0 by default and is
    not derived from any other cells, but is a factor
    in calculating the remaining amount due on the
    loan.
  • You can see where the Balance, Interest,
    Principal and Remaining columns would logically
    be based on some of the summary information, the
    current period and/or data from a previous
    period, or row.
  • Setting up these formulas and functions correctly
    will allow you to easily and quickly fill out the
    entire payment schedule, but this will require
    some special attention and adjustments to those
    expressions.

19
A cell range created by copy and paste
The cells within the dark border have been copied
from the cells in the dotted border.
20
Problems using copy and paste with formulas
  • When Excel does not have enough room to display
    an entire value in a cell, it uses a string of
    these symbols to represent that value. To view
    the values, increase the width of the H and J
    columns.
  • The formula in cell J5 is F5-(H5I5) and this
    was pasted into cell J6 by updating the cell
    references there to F6-(H6I6).
  • Cell G5 has the formula F5B5/B7 and cell G6
    contains F6B6/B8. This is where things went
    wrong. Sometimes this automatic update is very
    useful and other times it does not give you the
    desired result for your worksheet.
  • In this case, cells B5 and B7 should be
    referenced in the formula in column G in all 240
    payment period rows, but in column J, you want
    the cell references to be automatically updated.
    You can control this result using relative and
    absolute references.

21
Use relative references
  • A relative reference is a cell reference that
    shifts when you copy it to a new location on a
    worksheet.
  • A relative reference changes in relation to the
    change of location.
  • If you copy a formula to a cell three rows down
    and five columns to the right, a relative
    reference to cell B5 in the source cell would
    become G8 in the destination cell.

22
Use absolute references
  • An absolute reference is a cell reference that
    does not change when you copy the formula to a
    new location.
  • To create an absolute reference, you preface the
    column and row designations with a dollar sign
    ().
  • For example, the absolute reference for B5 would
    be B5.
  • This cell reference would stay the same no matter
    where you copied the formula.

23
Use mixed references
  • A mixed reference combines both relative and
    absolute cell references.
  • You can effectively lock either the row or the
    column in a mixed reference.
  • For example, in the case of B5, the row
    reference would shift, but the column reference
    would not
  • In the case of B5, the column reference would
    shift, but the row reference would not
  • You can switch between absolute, relative and
    mixed references in the formula easily in the
    edit mode or on the formula bar by selecting the
    cell reference in your formula and then pressing
    the F4 key repeatedly to toggle through the
    reference options.

24
Use Excel's Auto Fill features
  • When you need to copy and paste a large number of
    rows or columns, you can use a technique called
    Auto Fill using the fill handle.
  • The fill handle is a small black square located
    in the lower-right corner of a selected cell or
    range.
  • When you drag the fill handle, Excel
    automatically fills in the formulas and formats
    used in the selected cells.
  • The same rules for relative, absolute, and mixed
    references apply for Auto Fill as for copy and
    paste.

25
Auto Fill features
  • The Fill behavior depends on the date(s) with
    which you start.
  • If you select two or more dates separated by a
    month, then Excel will increment the auto filled
    dates by a month as well.
  • If you select only one date, Excel will increment
    the dates by one day only.
  • When you drag the fill handle, a small Auto Fill
    Options button appears to the lower right of the
    selected cell or range.
  • Common options are Copy Cells, Fill Series, Fill
    Formatting Only and Fill Without Formatting
  • If you are using the Auto Fill technique for
    dates, you have additional options to either Fill
    Days, Fill Weekdays, Fill Months or Fill Years.

26
Using Auto Fill
  • To use the Auto Fill feature, select the cell
    range that contains the values and/or formulas
    you want to copy.
  • Click and drag the fill handle in the direction
    you want to copy and then release the mouse
    button.
  • If needed, click the Auto Fill Options button,
    and then select the Auto Fill option you want to
    apply to the selected range.

27
An example of Auto Fill
This figure shows the payment schedule completed
for all 240 payments of the mortgage example.
Note the Auto Fill Options button to the lower
right of the fill handle.
If you were to click the Auto Fill Options
button, you would then see the menu shown below.
28
Auto Fill Options button menu options
  • The Copy Cells option will copy all values and
    formulas into the selected range as well as the
    formats used to display those values and
    formulas.
  • The Fill Series option copies values and formulas
    and completes any arithmetic or geometric series.
  • The Fill Formatting Only option copies only the
    formats used to display values or formulas
    without copying the values and formulas
    themselves.
  • The Fill Without Formatting option copies only
    the values and formulas without any of the
    formats used in the source range.
  • If you select a date series for filling, you will
    also see the Fill Days, Fill Weekdays, Fill
    Months and Fill Years options appear.

29
Create logical functions
  • A function that determines whether a condition is
    true or false is called a logical function.
  • Excel supports several logical functions such as
    AND, FALSE, IF, NOT, OR and TRUE.
  • A very common function is the IF function, which
    uses a logical test to determine whether an
    expression is true or false, and then returns one
    value if true or another value if false.
  • The logical test is constructed using a
    comparison operator that compares two expressions
    to determine if they are equal, not equal, if one
    is greater than the other, and so forth.
  • The comparison operators are , , ,
  • You can also make comparisons with text strings.
    You must enclose text strings within quotation
    marks.

30
Using the If function
  • The arguments for the IF function are
  • IF(logical_test,value_if_true,value_if_false)
  • For example, the function IF(A110,20,30) tests
    whether the value in cell A1 is equal to 10
  • If it is, the function returns the value 20,
    otherwise the function returns the value 30
  • Cell A1 could be empty or contain anything else
    besides the value 10 and the logical test would
    be false therefore, the function returns the
    value 30
  • To insert an IF function, click the Insert
    Function button and search for the IF function,
    then click OK.
  • When the Function Arguments dialog box appears,
    simply fill in the arguments.

31
Excel's date functions
  • Excel stores dates as integers, where the integer
    value represents the number of days since January
    1, 1900.
  • For example, the integer value for the date
    January 1, 2008 is 39448 because that date is
    39,448 days after January 1, 1900
  • You typically do not see these numbers, because
    Excel automatically formats them to appear in a
    date format.
  • This method of storing dates allows you to work
    with dates the same way you work with numbers.
  • Excel's commonly used date functions are DATE,
    DAY, MONTH, NOW, TODAY, WEEKDAY and YEAR.

32
The TODAY and Now functions
  • The TODAY and NOW functions always display the
    current date and time.
  • You will not normally see the time portion unless
    you have formatted the cell to display it.
  • If you use the TODAY or NOW function in a cell,
    the date in the cell is updated to reflect the
    current date and time of your computer each time
    you open the workbook.

33
Commonly used date functions
Since dates are stored as integers, you can
subtract one date from another to see how many
days there are separating the two dates. The
figure below provides additional details about
the common date functions in Excel.
34
Use a formula to enter the date
On the Documentation worksheet for the mortgage
analysis workbook, there is a place for the date,
as shown in the figure below. If you wanted a
fixed date to remain in cell B4, you would enter
that date. If you wanted the date in this cell to
always reflect the current date and time when you
opened the workbook, you would use the expression
NOW() or TODAY() as shown in the formula bar in
the figure.
Write a Comment
User Comments (0)
About PowerShow.com