Exploring Microsoft Office Excel 2007ObjectivesObjectives (continued)Formula BasicsCreating a FormulaCopy Formulas with Fill HandleRelative vs. Absolute AddressingFunctionsFunctions (continued)Using AutoSum ()Basic Statistical FunctionsDate FunctionsLogical and Lookup FunctionsIF FunctionVLOOKUPVLOOKUP (continued)Financial FunctionsPMT FunctionFV FunctionSlide 20Copyright © 2008 Pearson Prentice Hall. All rights reserved.11Copyright © 2008 Prentice-Hall. All rights reserved.Committed to Shaping the Next Generation of IT Experts.Chapter 2:Formulas and FunctionsRobert Grauer, Keith Mulbery, Judy Scheeren Exploring Microsoft Office Excel 2007Copyright © 2008 Pearson Prentice Hall. All rights reserved.2ObjectivesCreate and copy formulasUse relative and absolute cell addressesUse AutoSumInsert basic statistical functionsUse date functionsCopyright © 2008 Pearson Prentice Hall. All rights reserved.3Objectives (continued)Use the IF functionUse the VLOOKUP functionUse the PMT functionUse the FV functionCopyright © 2008 Pearson Prentice Hall. All rights reserved.4Formula BasicsFormulas are used to perform mathematical operations and arrive at a calculated result Must begin with an equals (=) signContain mathematical operators Used to automate calculations that were once done manuallyCopyright © 2008 Pearson Prentice Hall. All rights reserved.5Creating a FormulaRather than typing a cell address, use an alternative method that involves minimal typing Pointing uses the mouse or arrow keys to select the cell directly when creating a formulaCopyright © 2008 Pearson Prentice Hall. All rights reserved.6Copy Formulas with Fill HandleUse the fill handle, a small black square in the bottom right corner of a selected cell, to copy formulasProvides a clear-cut alternative method for copying the contents of a cell Can be used to duplicate formulasCopyright © 2008 Pearson Prentice Hall. All rights reserved.7Relative vs. Absolute AddressingRelative cell references change relative to the direction in which the formula is copiedAbsolute cell references are exact; they do not change when a formula is copiedIndicated by dollar ($) signs in front of the column letter and row numberMost often used when the value need not change, such as a sales tax percentage.Use the F4 key to toggle between relative and absolute cell referencingCopyright © 2008 Pearson Prentice Hall. All rights reserved.8FunctionsA predefined formula that can be selected from a listAlready has the formula information; just requires cell referencesDo not replace all formulasTake values, perform operations, and return resultsCopyright © 2008 Pearson Prentice Hall. All rights reserved.9Functions (continued)SUM is the most commonly used functionrepresented by a sigma ()Adds values within a specified rangeSyntax refers to the grammatical structure of a formulaMust adhere to stated structure of formulaArguments are values ─ used as input and returned as outputFunction Wizard automates entering the function formulasCopyright © 2008 Pearson Prentice Hall. All rights reserved.10Using AutoSum ()Automates the SUM functionClick the cell where you want the resultClick AutoSum buttonSelect the range of cells you want to sumPress Enter to completeAn example of AutoSum,=Sum(C4:C10) represents sum of all the cells in the cell range C4 to C10Copyright © 2008 Pearson Prentice Hall. All rights reserved.11Basic Statistical FunctionsPerform a variety of calculations to aide in decision making processAVERAGE calculates the average of a range of numbersMIN calculates the minimum value in a rangeMAX calculates the maximum value in a rangeCOUNT counts the number of values within a rangeMEDIAN finds the midpoint value in a rangeCopyright © 2008 Pearson Prentice Hall. All rights reserved.12Date 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 sideCopyright © 2008 Pearson Prentice Hall. All rights reserved.13Logical and Lookup FunctionsLogical functions help in decision makingLookup functions are very useful for looking up data entered in a specific range of cellsExample: Well suited well for tax tablesSearches for a value based on a cell referenceTwo types: VLOOKUP and HLOOKUPVLOOKUP arranges data verticallyHLOOKUP arranges data horizontallyCopyright © 2008 Pearson Prentice Hall. All rights reserved.14IF FunctionUsed to determine whether a condition has been metHas three arguments:a condition tested to determine if it is true or falsethe resulting value if the condition is truethe resulting value if the condition is falseWhen the condition is met, the formula performs one task; when it is not met, the formula performs another taskCopyright © 2008 Pearson Prentice Hall. All rights reserved.15VLOOKUPAllows for lookup within a vertical table of informationWell suited for large tables of data, such as tax tablesHas three arguments:a lookup value stored in a cella range of cells containing a lookup tablethe number of the column within the lookup table that contains the value to returnCopyright © 2008 Pearson Prentice Hall. All rights reserved.16VLOOKUP (continued)The lookup value ─ value to look up in a reference table The lookup table ─ a range of cells containing the reference tableA breakpoint ─ is the lowest numeric value for a category or series The column index number ─ the column number in the lookup table that contains return valuesCopyright © 2008 Pearson Prentice Hall. All rights reserved.17Financial FunctionsUsed for decisions involving payments, investments, interest rates, etc.Allows you to consider several alternativesCopyright © 2008 Pearson Prentice Hall. All rights reserved.18PMT FunctionUsed to calculate loan paymentsHas three arguments:the interest rate per periodthe number of periodsthe amount of the loanComputes the associated payment on a loanCopyright © 2008 Pearson Prentice Hall. All rights reserved.19FV FunctionUsed to determine the future value of an amount, such as an investmentHas three arguments:The interest rate (also called the rate of return)The