Excel Working with Functions
Adapted by Miguel Vlez Rubio From: Course Technology Cengage Learning oc.course.com/np/office2007 Excel Tutorial 3 October 2011
COMPREHENSIVE
Objectives
Use AutoFill to fill in a formula and complete a series Review function syntax Insert a function with the Insert Function dialog box Search for a function Type a function directly in a cell
XP
New Perspectives on Microsoft Office Excel 2007
Objectives
Insert the date with the TODAY function Enter the IF logical function Calculate monthly mortgage payments with the PMT financial function
XP
New Perspectives on Microsoft Office Excel 2007
AutoFill
New Perspectives on Microsoft Office Excel 2007
COMPREHENSIVE 4
Working with AutoFill
XP
AutoFill copies content and formats from a cell or range into an adjacent cell or range Select the cell or range that contains the formula or formulas you want to copy Drag the fill handle in the direction you want to copy the formula(s) and then release the mouse button To copy only the formats or only the formulas, click the AutoFill Options button and select the appropriate option
New Perspectives on Microsoft Office Excel 2007
Working with AutoFill
XP
New Perspectives on Microsoft Office Excel 2007
Using the AutoFill Options Button
XP
By default, AutoFill copies both the formulas and the formats of the original range to the selected range You can specify what is copied by using the AutoFill Options button that appears after you release the mouse button
Show in Excel
New Perspectives on Microsoft Office Excel 2007 7
Filling a Series
AutoFill can also be used to create a series of numbers, dates, or text based on a pattern
XP
Show in Excel
New Perspectives on Microsoft Office Excel 2007 8
Filling a Series
XP
New Perspectives on Microsoft Office Excel 2007
Creating a Series with AutoFill
XP
Enter the first few values of the series into a range Select the range, and then drag the fill handle of the selected range over the cells you want to fill or Enter the first few values of the series into a range Select the entire range into which you want to extend the series In the Editing group on the Home tab, click the Fill button, and then click Down, Right, Up, Left, Series, or Justify to set the direction you want to extend the series
Show in Excel
New Perspectives on Microsoft Office Excel 2007 10
Functions
New Perspectives on Microsoft Office Excel 2007
COMPREHENSIVE 11
Understanding Function Syntax
XP
Every function has to follow a set of rules, or syntax, which specifies how the function should be written
Arguments
New Perspectives on Microsoft Office Excel 2007
12
Understanding Function Syntax
XP
New Perspectives on Microsoft Office Excel 2007
13
Inserting a Function
XP
Click the Formulas tab on the Ribbon To insert a function from a specific category, click the appropriate category button in the Function Library group. To search for a function, click the Insert Function button in the Function Library group, enter a description of the function, and then click the Go button Select the appropriate function from the list of functions Enter the argument values in the Function Arguments dialog box, and then click the OK button
New Perspectives on Microsoft Office Excel 2007 14
Inserting a Function
XP
New Perspectives on Microsoft Office Excel 2007
15
Inserting a Function
XP
New Perspectives on Microsoft Office Excel 2007
16
Typing a Function
As you begin to type a function name within a formula, a list of functions that begin with the letters you typed appears
XP
New Perspectives on Microsoft Office Excel 2007
17
Working with Date Functions
XP
Show in Excel
New Perspectives on Microsoft Office Excel 2007 18
Working with Logical Functions
A logical function is a function that works with values that are either true or false The IF function is a logical function that returns one value if the statement is true and returns a different value if the statement is false IF(logical_test, value_if_true, [value_if_false])
XP
New Perspectives on Microsoft Office Excel 2007
19
Working with Logical Functions
XP
A comparison operator is a symbol that indicates the relationship between two values
Show in Excel
New Perspectives on Microsoft Office Excel 2007 20
Working with Logical Functions
=IF(A1="YES", "DONE", "RESTART") =IF(A1="MAXIMUM", MAX(B1:B10), MIN(B1:B10)) =IF(D33>0, $K$10, 0)
XP
New Perspectives on Microsoft Office Excel 2007
21
Working with Logical Functions
XP
Show in Excel
New Perspectives on Microsoft Office Excel 2007 22
Working with Financial Functions
XP
New Perspectives on Microsoft Office Excel 2007
23
Using the PMT Function to Determine a Monthly Loan Payment
XP
For loan or investment calculations, you need to know the following information:
The annual interest rate The payment period, or how often payments are due and interest is compounded The length of the loan in terms of the number of payment periods The amount being borrowed or invested
PMT(rate, nper, pv, [fv=0] [type=0])
New Perspectives on Microsoft Office Excel 2007 24
Using the PMT Function to Determine a Monthly Loan Payment
XP
New Perspectives on Microsoft Office Excel 2007
25
Using the PMT Function to Determine a Monthly Loan Payment
XP
Show in Excel
New Perspectives on Microsoft Office Excel 2007 26
