Download
chapter 16 working with advanced functions n.
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter 16 Working with Advanced Functions PowerPoint Presentation
Download Presentation
Chapter 16 Working with Advanced Functions

Chapter 16 Working with Advanced Functions

304 Vues Download Presentation
Télécharger la présentation

Chapter 16 Working with Advanced Functions

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. Chapter 16Working with Advanced Functions Excel 2003, Volume 2 by Karen J. Jolly © Scott/Jones Publishers, Inc.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

  7. Format Time To display hours and minutes elapsed, use the Time category. © Scott/Jones Publishers, Inc.

  8. 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.

  9. 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.

  10. 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.

  11. 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.

  12. 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.

  13. Completed Hour & Minute Formulas Hour function Minute function © Scott/Jones Publishers, Inc.

  14. 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.

  15. 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.

  16. Complete Hours Worked • Enter formula to determine elapsed time. Use cell D7. © Scott/Jones Publishers, Inc.

  17. Convert cells to a decimal number, using the Hour and Minute functions. © Scott/Jones Publishers, Inc.

  18. Convert cells to a decimal number, using the Hour and Minute functions. © Scott/Jones Publishers, Inc.

  19. 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.

  20. 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.

  21. 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.

  22. 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.

  23. 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.

  24. 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.

  25. Differences in Annual Depreciation DoubleDeclining StraightLine Sum-of-Years © Scott/Jones Publishers, Inc.

  26. Summary ofDepreciation Functions © Scott/Jones Publishers, Inc.

  27. 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.

  28. 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.

  29. 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.

  30. 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.

  31. 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.

  32. 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.

  33. 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.

  34. 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.

  35. 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.

  36. 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.

  37. 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.

  38. Example:Enter Grades • Grades will be entered in column C. • The lookup table is range E5:F10. © Scott/Jones Publishers, Inc.

  39. 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.

  40. 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.

  41. VLookup Place results here Column 1 Column 2 Table Array Lookup Value © Scott/Jones Publishers, Inc.

  42. 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.

  43. 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.

  44. 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.

  45. Lookup From Another Worksheet Table array from worksheet names Prize Schedule © Scott/Jones Publishers, Inc.

  46. 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.

  47. AND Function Dialog Box Person must be located in Portland AND Purchases > $1,200 © Scott/Jones Publishers, Inc.

  48. Results © Scott/Jones Publishers, Inc.

  49. 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.

  50. OR Function Dialog Box Located in Portland OR Purchased > $1,200 © Scott/Jones Publishers, Inc.