Créer une présentation
Télécharger la présentation

Télécharger la présentation
## Chapter 16 Working with Advanced Functions

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -

**Chapter 16Working with Advanced Functions**Excel 2003, Volume 2 by Karen J. Jolly © Scott/Jones Publishers, Inc.**Outcomes**• Complete logical, date and time, and financial and statistical functions. • Convert elapsed time to decimals for use in payroll preparation. • Use a lookup table. © Scott/Jones Publishers, Inc.**Date and Time Functions**• The date and time functions provide many ways to enter the date or time. • Date and time functiojns may be used in formulas. © Scott/Jones Publishers, Inc.**Determine Elapsed Time**• Place start time in one cell. • Place ending time in an adjacent cell. • Create a formula to subtract starting time from ending time. © Scott/Jones Publishers, Inc.**About Elapsed Time**• For am/pm enter the first letter a or p. • If am/pm are used in formula results will be in am/pm. • Use Format Cells to change elapsed time number format. • Formulas also determine number of days elapsed. © Scott/Jones Publishers, Inc.**Elapsed Time Hints**• For am/pm enter the first letter a or p. • If am/pm are used in formula results will be in am/pm. • Use Format Cells to change elapsed time number format. • Formulas also determine number of days elapsed. © Scott/Jones Publishers, Inc.**Format Time**To display hours and minutes elapsed, use the Time category. © Scott/Jones Publishers, Inc.**Calculate Elapsed Days**• Enter start date in one cell. • Enter end date in adjacent cell. • Create a formula to subtract starting date from ending date. Excel converts dates into serial numbers © Scott/Jones Publishers, Inc.**Hour and Minute Functions**• The hour function returns the hour of a cell reference. The number ranges from 0 (for 12 am) to 23 (for 12 pm). • The minute function returns the minute as a number from 0 to 59. • When time is expressed with a colon for hours and minutes (1:45), the hour and minute functions can be used to convert the time to a decimal number. © Scott/Jones Publishers, Inc.**Hour and Minute Functions**• When time is expressed with a colon for hours and minutes (1:45), the hour and minute functions can be used to convert the time to a decimal number. • Payroll uses hours and minutes to determine hours worked. © Scott/Jones Publishers, Inc.**Hour Function**• In an adjacent cell: • Access the Insert Function dialog box. • In Date and Time category select Hour function. • Complete the serial argument by selecting cell that contains the hour. © Scott/Jones Publishers, Inc.**Minute Function**• Convert time to minutes. • Select a cell that does not contain the time. • Access Insert Function dialog box. • Select minute function. • Complete serial argument by selecting cell that contains the minute. © Scott/Jones Publishers, Inc.**Completed Hour & Minute Formulas**Hour function Minute function © Scott/Jones Publishers, Inc.**Convert Time to a Decimal Fraction**• Determining the hours worked for payroll reports is an important use for Excel. • Determine the time elapsed from the starting to ending time. • Convert time to a decimal fraction. • Use decimal function to complete the payroll. © Scott/Jones Publishers, Inc.**Convert Hours Worked to Decimal Fraction**• Determine cell where formula will be entered. • Use Hour function to calculate hours. • Use Minute function to calculate minutes. • Create formula to add them together. • Divide minute function by 60. Completed formula © Scott/Jones Publishers, Inc.**Complete Hours Worked**• Enter formula to determine elapsed time. Use cell D7. © Scott/Jones Publishers, Inc.**Convert cells to a decimal number, using the Hour and Minute**functions. © Scott/Jones Publishers, Inc.**Convert cells to a decimal number, using the Hour and Minute**functions. © Scott/Jones Publishers, Inc.**Depreciation Functions**• Financial Function category. • An asset decreases in value because of obsolescence or wear and tear. • This decrease in the value of an asset is called depreciation. • The depreciation functions covered in this text • Straight-line • Declining-Balance • Sum-of-years’-digits © Scott/Jones Publishers, Inc.**Depreciation Function Arguments**• Cost – the amount paid for the asset. • Life – the estimated useful life of the asset. • Salvage – the estimated value of an asset at the end of its life. • Period – the period in the life of the asset on which depreciation is being calculated. © Scott/Jones Publishers, Inc.**Straight Line Depreciation**• The SLNmethod (Straight-LiNe) depreciates the asset in equal amounts over its life. • Straight line arguments are cost, life, and salvage. © Scott/Jones Publishers, Inc.**Depreciation Schedule**• A depreciation scheduletraces the amount of depreciation over the life of the asset. • Book value is the cost less the total depreciation. • When fully depreciated, book value should equal salvage value. © Scott/Jones Publishers, Inc.**Declining-Balance Depreciation**• TheDB depreciation method (Declining-Balance) follows the theory that an asset depreciates more at the beginning of its use than at the end. • Declining-balance uses the cost, life, salvage, and period arguments. © Scott/Jones Publishers, Inc.**Sum-of-Years’ Depreciation**• The Sum-of-Years’ Digits (SYD) depreciation method recognizes that an asset depreciates faster during the early years of its life. • The arguments are Cost, Salvage,Life,andPer(iod). © Scott/Jones Publishers, Inc.**Differences in Annual Depreciation**DoubleDeclining StraightLine Sum-of-Years © Scott/Jones Publishers, Inc.**Summary ofDepreciation Functions**© Scott/Jones Publishers, Inc.**Present Value Function**• Present Value is the value of money today. • It is how much a stream of future payments is worth today. • It is used to determine how much to invest today to meet a future need. • It is useful for retirement planning or saving for future needs, such as a down payment on a home. © Scott/Jones Publishers, Inc.**Present Value (PV)**• Rate – the interest rate to be earned on the investment. • Nper– the number of periods the investment will be made. • Pmt – the amount of each period’s investment. • FV – the amount needed at the end of the investment period. © Scott/Jones Publishers, Inc.**Facts About the PV Function**• It is used to evaluate a long-term investment. • PV is today’s value of any investment. • It takes into account payments, interest rate, and time the money will be invested. • Assumes investment will be a series of equal payments or one lump-sum payment. © Scott/Jones Publishers, Inc.**About the PV Function**• If lump-sum payment, do not enter payment argument. • If a series of equal payments, use the payment argument and leave the FV argument empty. • Use Typeargument when PMT argument is used. • 1 = payment made at the beginning of the period • 2 = payment made at the end of the period © Scott/Jones Publishers, Inc.**Future Value (FV)**• Future value computes the value of today’s money at a future date. • The investment can be a lump sum payment of a series of equal payments. © Scott/Jones Publishers, Inc.**Future Value (FV)**• Note that arguments are the same as the PV dialog box, EXCEPT that in this dialog box the FV argument is now the PV argument. © Scott/Jones Publishers, Inc.**Facts About the FV Function**• Used to evaluate an investment. • FV is today’s value of today’s investment at a future time. • It takes into account payments, interest rate, and time the money will be invested. • It assumes the investment will be a series of equal payments or one lump-sum payment. © Scott/Jones Publishers, Inc.**About the FV Function**• Either PMT or PV argument must be used. • Use PMT is equal payments are made each period. • Use PV when a lump sum payment is made now. • Use Type argument when PMT argument is used. • 1 = payment made at the beginning of the period • 2 = payment made at the end of the period © Scott/Jones Publishers, Inc.**Lookup Functions**A lookup function looks up information in a table and transfers the information from the table into a worksheet. The table that is used is called a lookup table. © Scott/Jones Publishers, Inc.**Facts About Lookups**• One number is compared to a group of numbers in the lookup table. • When a match is found, a value is placed in the worksheet. • If an exact match is not made, Excel uses next lower number. • A lookup table may be placed on any worksheet of the workbook or in a different workbook. © Scott/Jones Publishers, Inc.**Lookup Function Arguments**• Lookup value - a value in the worksheet that will be compared to the lookup table. • Table array - the table of text, numbers, or logical values that contains the comparison value that will be entered into the worksheet. • Column index number - the number of the column in the table array that contains the information that will be entered into the worksheet cell. © Scott/Jones Publishers, Inc.**Example:Enter Grades**• Grades will be entered in column C. • The lookup table is range E5:F10. © Scott/Jones Publishers, Inc.**VLookup Function**• Vertical Lookup table contains at least two columns. • Col 1 – the value that will be compared with data • Col 2 – the entry that will be returned in the cell © Scott/Jones Publishers, Inc.**VLookup Dialog Box**VLookup is a Lookup & Reference function. Cell containing data to be compared to table Enter lookup table as absolute cell references Column number of value to be returned © Scott/Jones Publishers, Inc.**VLookup**Place results here Column 1 Column 2 Table Array Lookup Value © Scott/Jones Publishers, Inc.**Facts About VLookups**• The data table must be in ascending value. • Express the Table Array as absolute value. • The Column Index Number is the column that contains the data to be returned to the cell. • Excel looks in the first column to find a match. If match not made, looks to next lowest number. © Scott/Jones Publishers, Inc.**More VLookup Facts**• Use single number for lookup. If the range is 35-50, use the lowest number, 35. • Begin with the smallest number, 0 or 1. • If lookup is text, arrange text in alphabetical order. © Scott/Jones Publishers, Inc.**HLookup Function**• Dialog box is identical to Vlookup, • Table array is laid out in rows (horizontally) instead of columns (vertically). • The top row must contain the compar- ison data. © Scott/Jones Publishers, Inc.**Lookup From Another Worksheet**Table array from worksheet names Prize Schedule © Scott/Jones Publishers, Inc.**AND Function**• Result is either true or false. • All arguments must be true to return True. • Example: Send invitation to people who live in Portland AND who have purchase more than $1,200. © Scott/Jones Publishers, Inc.**AND Function Dialog Box**Person must be located in Portland AND Purchases > $1,200 © Scott/Jones Publishers, Inc.**Results**© Scott/Jones Publishers, Inc.**OR Function**• The OR function is a logical function that gives a result of true or false. • In order for the statement to be true, any one of the arguments needs to be met. • Example: Invitations will be sent to anyone in located in Portland OR anyone purchasing $1,200 or more. © Scott/Jones Publishers, Inc.**OR Function Dialog Box**Located in Portland OR Purchased > $1,200 © Scott/Jones Publishers, Inc.