Chapter 16 Working with Advanced Functions

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.