Excel 2013 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management

2. Excel 2013 Level 2 Unit 1Advanced Formatting, Formulas, and Data Management Chapter 2Advanced Functions and Formulas

3. Advanced Functions and Formulas Quick Links to Presentation Contents • Name Ranges • Statistical Functions • Math and Trigonometry Functions • CHECKPOINT 1 • Manage Range Names • Lookup Functions • Financial Functions • Logical Functions • CHECKPOINT 2

4. Name Ranges

5. Name Ranges - continued To create a range name: • Select desired cells. • Click in Name box located at the left end of Formula bar. • Type desired range name. • Press Enter. Name box

6. Name Ranges - continued • When creating a name for a cell or a range of cells, the following naming rules apply: • Names can be a combination of letters, numbers, underscore characters, or periods, up to 255 characters. • The first character must be a letter, an underscore, or a backslash (\). • Spaces are not valid. Use underscore characters or periods to separate words. • A valid cell address cannot become a range name. • Range names are not case sensitive.

7. Statistical Functions • Commonly used statistical functions include AVERAGE, MAX, and MIN, where AVERAGE returns the arithmetic mean, MAX returns the largest value, and MIN returns the smallest value in the range. • Another function used often is COUNT, which returns the number of cells that contain numbers or dates. • Empty cells, text labels, or error values in the range are ignored.

8. Statistical Functions - continued • In a worksheet that requires cells containing text, or cells containing a combination of text and numbers (such as Model-2146) to be counted, Excel provides the COUNTA function. Formula =COUNT(A2:A6) returns zero Formula =COUNTA(A2:A6) returns the correct result

9. Statistical Functions - continued • Use the COUNTIF function to count the number of cells within a range that meet a single criterion. • COUNTIFS is used to count cells that meet multiple criteria. Formula =COUNTIF(Title,"RN") Formula =COUNTIFS(Title,"RN",PDCurrent,"Yes")

10. Statistical Functions - continued To create a COUNTIF formula: • Make desired cell active. • Click Insert Function button. • At Insert Function dialog box, change category to Statistical option. • Select COUNTIF option. • Click OK. continues on next slide… COUNTIF option

11. Statistical Functions - continued • Enter range address or range name to select by in Range text box. • Enter condition expression or text in Criteriatext box. • Click OK. Criteria text box

12. Statistical Functions - continued To create a COUNTIFS formula: • Make desired cell active. • Click Insert Function button. • At Insert Function dialog box, change category to Statistical option. • Select COUNTIFS option. • Click OK. continues on next slide… COUNTIFS option

13. Statistical Functions - continued • Enter range address or range name to select by in Criteria_range1text box. • Enter condition expression or text in Criteria1text box. • Enter range address or range name to select by in Criteria_range2text box. • Enter condition expression or text in Criteria2text box. • Continue adding criteria range expressions and criteria as needed. • Click OK. Criteria2 text box

14. Statistical Functions - continued • The AVERAGEIF function is used to find the arithmetic mean of the cells within the specified range that meet a single criterion. • The AVERAGEIFS function is used to average cells that meet multiple criteria. Formula =AVERAGEIF(Campus,"Portland",Total) Formula =AVERAGEIFS (Total,Campus,"Sunnyside",Year,"<2013")

15. Statistical Functions - continued To create an AVERAGEIF formula: • Make desired cell active. • Click Insert Function button. • At Insert Function dialog box, change category to Statistical option. • Select AVERAGEIF option. • Click OK. continues on next slide… AVERAGEIF option

16. Statistical Functions - continued • Enter range address or range name to select by in Range text box. • Enter condition expression or text in Criteriatext box. • Enter range address or range name to average in Average_range text box. • Click OK. Average_range text box

17. Statistical Functions - continued To create an AVERAGEIFS formula: • Make desired cell active. • Click Insert Function button. • At Insert Function dialog box, change category to Statistical option. • Select AVERAGEIFS option. • Click OK. continues on next slide… AVERAGEIFS option

18. Statistical Functions- continued • Enter range address or range name to average in Average_rangetext box. • Enter range address or range name to select by in Criteria_range1 text box. • Enter condition expression or text in Criteria1text box. • Enter range address or range name to select by in Criteria_range2text box. • Enter condition expression or text in Criteria2text box. • Continue adding criteria range expressions and criteria as needed. • Click OK. Average_range text box

19. Math and Trigonometry Functions • Within the math and trigonometry function category, Excel includes SUMIF to add the cells within a range that meet a single criterion and SUMIFS to add the cells within a range that meet multiple criteria. Formula =SUMIF(Supplier,"101",StdCost) Formula =SUMIFS(StdCost,Supplier,"350",MinQty,">4")

20. Math and Trigonometry Functions -continued To create a SUMIF formula: • Make desired cell active. • Click FORMULAS tab. • Click Math & Trig button. • Scroll down and click SUMIFoption. continues on next slide… SUMIF option

21. Math and Trigonometry Functions -continued • Enter range address or range name to select by in Range text box. • Enter condition expression or text in Criteria text box. • Enter range address or range name to add in Sum_range text box. • Click OK. Sum_range text box

22. CHECKPOINT 1 • Assigning this to a cell or a range of cells allows you to reference the source by a descriptive label. • function • code • number • name • Use this function to count cells within a range that meet a single criterion. • AVERAGEIF • COUNTIF • AVERAGEIFS • COUNTIFS Answer Answer Next Question Next Question • Range names can be up to this many characters. • 255 • 155 • 100 • 25 • This function is used to average cells that meet multiple criteria. • AVERAGEIF • COUNTIF • AVERAGEIFS • COUNTIFS Answer Answer Next Question Next Slide

23. Manage Range Names To edit a range name: • Click FORMULAS tab. • Click Name Manager button. • Click desired range name. • Click Edit button. continues on next slide… Edit button

24. Manage Range Names -continued • Type a new range name in Name text box. • Click OK. • Click Close. Name text box

25. Manage Range Names - continued To delete a range name: • Click FORMULAS tab. • Click Name Manager button. • Click desired range name. • Click Delete button. • Click OK. • Click Close. Delete button

26. Lookup Functions • The Lookup & Reference category of functions provides formulas that can be used to look up values in a range. • Excel provides two lookup functions: VLOOKUP and HLOOKUP, which refer to a vertical or horizontal lookup, respectively. • The structure of a VLOOKUP formula is =VLOOKUP(lookup_value,table_array,col_index_num,range_lookup). • The structure of an HLOOKUP formula is =HLOOKUP(lookup_value,table_array,row_index_num,range_lookup).

27. Lookup Functions - continued

28. Lookup Functions - continued Lookup table is named grid. VLOOKUP formula populates E4:E13 by matching the salary grid rating number in column D with the corresponding salary grid rating number in the lookup table named grid. Formula in E4 is =VLOOKUP(Rating,grid,2).

29. Lookup Functions - continued To create a VLOOKUP formula: • Make desired cell active. • Click FORMULAS tab. • Click Lookup & Reference button. • Click VLOOKUP option. continues on next slide… VLOOKUP option

30. Lookup Functions - continued • Enter cell address, range name, or value in Lookup_valuetext box. • Enter range or range name in Table_array text box. • Type column number to return values from in Col_index_num text box. • Type FALSE or leave blank for TRUEin Range_lookup text box. • Click OK. Range_lookup text box

31. Lookup Functions - continued • The HLOOKUP function uses the same argument parameters as VLOOKUP. Lookup table is Named GradeTable. HLOOKUP formula populates G4:G12 by looking up the total value in column F with the first row in GradeTable. Excel stops at the largest value in the table that does not go over the lookup value. Looking for 62.3 would cause Excel to stop at 60 because moving to the next value, 70, would be over the lookup value. Formula in G4 is =HLOOKUP(F4,GradeTable,2).

32. Financial Functions • Financial functions can be used for a variety of financial analyses including loan amortizations, annuity payments, investment planning, depreciation, and so on. • The PMT function is used to calculate a payment for a loan based on a constant interest rate and constant payments for a set period of time. • Excel provides two related financial functions: PPMT, to calculate the principal portion of the loan payment; and IPMT, to calculate the interest portion.

33. Financial Functions - continued • The PPMT function returns the principal portion of a specific payment for a loan. • The structure of a PPMT function is =PPMT(rate,per,nper,pv,fv,type) where: • rate is the interest rate per period, • per is the period for which you want to find the principal portion of the payment, • nper is the number of payment periods, • pv is the amount of money borrowed, • fv is the balance at the end of the loan (if left blank, zero is assumed), and • type is either 0 (payment at end of period) or 1 (payment at beginning of period).

34. Financial Functions - continued To create a PPMT formula: • Make desired cell active. • Click FORMULAS tab. • Click Financial button. • Click PPMT option. continues on next slide… PPMT option

35. Financial Functions - continued • Enter value, cell address, or range name for interest rate in Rate text box. • Enter number representing payment to find principal for inPer text box. • Enter value, cell address, or range name for total number of payments in Nper text box. • Enter value, cell address, or range name for amount borrowed in Pv text box. • Click OK. Pvtext box

36. Logical Functions • Conditional logic in formulas requires Excel to perform a calculation based on the outcome of a conditional test where one calculation is performed if the test proves true and another calculation is performed if the test proves false. • If you need Excel to perform more than two actions, create a nested IF function. • You can nest any function inside of another function.

37. Logical Functions - Continued • Consider the following formula: • In the first IF function, the conditional test is to determine if the sales value is less than $40,000 (Sales<40000). • If the test proves true (for example, sales are $25,000), then Excel calculates the sales times 5% and returns the result in the active cell. continues on next slide… =IF(Sales<40000,Sales*5%,IF(Sales<80000,Sales*7%,Sales*9%))

38. Logical Functions - Continued =IF(Sales<40000,Sales*5%,IF(Sales<80000,Sales*7%,Sales*9%)) • If the first test is not true, then Excel reads the next section of the argument, which is the next IF function that includes the conditional test to determine if sales are less than $80,000 (Sales<80000). • If this second conditional test proves true, then Excel calculates the sales times 7%. • If the test proves false, Excel calculates the sales times 9%. • Since these are the only three possible actions, the formula ends.

39. Logical Functions - continued To create an IF Formula: • Make desired cell active. • Click FORMULAS tab. • Click Logical button. • Click IF function. • Type conditional test argument in Logical_test text box. • Press Tab. • Type argument in Value_if_true text box. • Press tab. • Type argument in Value_if_false text box. • Click OK. Logical_test text box

40. Logical Functions - continued

41. Logical Functions - continued To create an AND Formula: • Make desired cell active OR nest formula in IF statement Logical_test text box. • Type =AND( or AND( if nesting in an IF statement. • Type first conditional test argument. • Type a comma. • Type second conditional test argument. • Repeat Steps 4 to 5 for remaining conditions. • Type ). Logical_test text box

42. Logical Functions - continued To create an OR Formula: • Make desired cell active OR nest formula in IF statement Logical_test text box. • Type =OR( or OR( if nesting in an IF statement. • Type first conditional test argument. • Type a comma. • Type second conditional test argument. • Repeat Steps 4 to 5 for remaining conditions. • Type ). Logical_test text box

43. CHECKPOINT 2 • The Name Manager button is located on this tab. • INSERT • FORMULAS • HOME • DATA • This function returns the principal portion of a specific payment for a loan. • SPMT • IPMT • PMT • PPMT Answer Answer Next Question Next Question • This lookup function is more commonly used since most lookup tables are arranged with comparison data in columns. • CLOOKUP • DLOOKUP • VLOOKUP • HLOOKUP • If you need to perform more than two actions create a(n) • nested IF function • SUMIF function • COUNTA function • embedded function Answer Answer Next Question Next Slide

44. Advanced Functions and Formulas Summary of Presentation Concepts • Create and use named ranges in formulas • Use functions COUNTA, COUNTIF, COUNTIFS • Use functions AVERAGEIF, AVERAGEIFS • Use functions SUMIF, SUMIFS • Edit a named range • Rename and delete a named range • Look up data using the lookup functions VLOOKUP and HLOOKUP • Analyze loan payments using PPMT • Use conditional logic functions IF, AND, and OR