Common Functions

1. Common Functions Chapter 6

2. Working with Functions • Precede with = if first in expression • Returns single answer • Value of function’s evaluation • Function name indicative of task • More than 240 functions available FunctionName(argument1,argument2, …) Argument List

3. Argument List • Argument list has 0 or more arguments • Zero argument is empty () • Gets argument from system • Still needs parentheses • Separate arguments with commas, no spaces • Positional arguments • Arguments listed in specific order • Inflexible • Necessary for correct substitution in hidden equation

4. Assumptions Section External Data Section Output Section Organizing a Worksheet into Sections Prototype (proposed model)

5. Assumptions Data • Example data used to test worksheet • Erased when convinced worksheet works properly

6. External Data • Data generated from external sources

7. Data Validation • Attempt to control invalid entries • Range errors • Values either too large, too small, or do not make sense for application • Data Validation Dialog Box • Restrict type and value of information entered into worksheet • Attach helpful reminder message to cell • Allows error alert message to appear if data entered is not allowed

8. Data Validation Choices

9. Input Message Example When cell is made active and user begins to type, the message appears with information about entry.

10. Error Alert Example • Specify error message for Excel to display if data does not meet criteria • Three levels of alert – Information, Warning and Stop (each with different icon)

11. Naming a Cell or Cell Range • Select cell or cell range • Two methods • Name box in formula bar • Insert, Name, Define • Worksheet name is part of definition • Cell reference is absolute • Name only appears in Name box if whole area is selected

12. Rules for Naming • Begin with letter or underscore • Remaining characters (letters, numbers, periods, and underscores) • Length 1 - 255 characters • Capitalization ignored • No spaces allowed • Cannot resemble cell references

13. Using Names in Formulas • Names used instead of addresses makes formulas more understandable • Color coded to match outlines around cells formula references

14. Deleting a Name Highlight name in list andclick on Delete

15. IF Function • Belongs to logical function category • Conditional test is equation comparing two values (functions, formula labels, or logical values) • Relational operator compares two parts of a formula • Result of comparison is either true or false If(conditional test, expression if true, expression if false)

16. Relational Operators • IF(logical test, value if true, value if false) • provide a choice of action based on one or more conditions • Comparison operator symbols • less than < • greater than > • less than or equal to <= • greater than or equal to >= • equal to = • not equal to <>

17. Results in an “empty” cell IF Examples A B C D E 1 8 5 4 3 9 2 7 6 5 8 10 3 1 2 3 7 6 =IF(D1>A2,”true”,”false”) 3>7 15 =IF(B3<D2,C2*D1,E2-C3) 2<8 =IF(C2=B1,””,”paid”) 5=5

18. LOOKUP Functions • Special class of functions • Use search value to search table (range of cells) for match or close match and return value • Lookup table (range of cells) • Lookup value (value being used to search) • Two lookup functions • HLOOKUP • Search first row for lookup value (horizontally) • VLOOKUP • Search first column for lookup value (vertically)

19. VLOOKUP • VLOOKUP(look-up value, table,col-num) • looks in first column of a table and moves across the row to return the value in the cell intersection • the lookup column (first one) MUST have the values sorted in ascending order • the lookup column is referenced as 1

20. Example: • VLOOKUP(45,C5:F8,3) • would look for the number 45 in the first column of the range C5:F8 • once found, it would move over to the 3rd column and return the value C D E F 5 6 7 8 10 34 54 67 20 44 76 90 35 66 24 11 56 39 98 84

21. 47 Another Example: 99 =VLOOKUP (47, table, 5) 23 45 32 11 12 34 44 65 77 88 99 24 61 87 91 74 66 42 78 69 30 40 50 61 86 53 78 98 73 61 44 - 60.9 99

22. D E F G H 3 4 5 6 7 8 9 10 Able 45 67 sally joe Cable 56 72 alice holly Even 73 88 betty james Given 66 11 susan hope Life 99 77 barb jim Odd 89 90 mary steve Soon 40 53 chris hip Union 55 10 bill joan Mom falls between Life and Odd if we were alphabetizing! Mom What if the first column has text? Since text has an order - you can alphabetize text - it will work in the first column. It must be in ascending order! 77 =Vlookup (“Mom”, $D$3:$H$10, 3)

23. Abe D E F G H Comes before Able 3 4 5 6 7 8 9 10 Able 45 67 sally joe Cable 56 72 alice holly Even 73 88 betty james Given 66 11 susan hope Life 99 77 barb jim Odd 89 90 mary steve Soon 40 53 chris hip Union 55 10 bill joan Another Example: =Vlookup (“Abe”, $D$3:$H$10, 4) #N/A Since there is no row above, then no value can be returned and an error is returned.

24. D E F G H 3 4 5 6 7 8 9 10 Able 45 67 sally joe Cable 56 72 alice holly Even 73 88 betty james Given 66 11 susan hope Life 99 77 barb jim Odd 89 90 mary steve Soon 40 53 chris hip Union 55 10 bill joan Comes after Union Zip Another Example: bill =Vlookup (“Zip”, $D$3:$H$10, 4) Row 10 covers values of Union and all values beyond.

25. Using Financial Analysis Functions • Many financial functions available • Fundamental concept is time value of money • Means receiving money today is more valuable than receiving it next year • Using the Function Wizard to build complicated functions • Popular financial functions • PMT, IPMT, PPMT, PV

26. PMT – Payment Function • Amortization – process of distributing periodic payments over life of a loan • PMT calculates periodic payment given • Principal (amount borrowed) • Rate (interest percentage) • Term (time period for payments) • PMT(rate,nper,pv) Return value will be negative because it is money being paid out! If you want it positive, place a negative in front of the function.

27. PMT Example What is the monthly payment for a $5,000 loan at 11% annual interest to pay back in 3 years? • =PMT(.11/12,36,5000) • Note: The annual rate (decimal form) is divided by 12 to get the monthly payment rate and the number of payments is enteredas number of months.

28. PV Function PV(rate,nper,pmt,fv,type) Returns the present value of an investment: the total amount that a series of future payments is worth now.

29. PPMT Function PPMT(rate,per,nper,pv,fv,type) Returns the payment on the principal for a given investment based on periodic, constant payments and a constant interest rate.

30. IMPT Function IPMT(rate,per,nper,pv,fv,type) Returns the interest payment for a given period for aninvestment, based on periodic, constant payments anda constant interest rate.

31. Working with Worksheet Tabs • Drag and drop tabs to change order • Right click to bring up short cut menu of options for tabs

32. Protection • Two-step process • Unlock cells so users can type new data • Enforce Excel protection rules by turning on worksheet protection • All cells in workbook are locked (default) • Protection is worksheet level – protection is independent within workbook • Good idea to color or change appearance of locked cells – user friendly

33. Enable worksheet protection Tools, Protection, Protect Sheet Protection of Cells and Worksheets

34. Protection of Cells and Worksheets • Can now enter password (optional) • Chose what users can do • Use Tab key to move between unprotected cells • Locked cell cannot become active

35. Summary – How to • Work with functions • Arrange worksheets into sections • Provide data validation for input data • Name cells or ranges and utilize them in formulas • Use some specific functions • IF, VLOOKUP, PMT, PPMT, IPMT, PV, NOW • Work with worksheet cell protection