1 / 46

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

Excel 2010 Level 2 Unit 1 Advanced Formatting, Formulas, and Data Management Chapter 2 Advanced Functions and Formulas. Advanced Functions and Formulas. Quick Links to Presentation Contents. Name Ranges Statistical Functions Math and Trigonometry Functions CHECKPOINT 1

dian
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. 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 • Text Functions • CHECKPOINT 2

  3. Name Ranges

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

  5. 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 within a range name. Use underscore characters or periods to separate words. • A valid cell address cannot become a range name. • Range names are not case sensitive.

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

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

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

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

  10. Statistical Functions…continued • Enter the range address or the range name to select by in the Range text box. • Enter the condition expression or text in the Criteria text box. • Click OK. Criteria text box

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

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

  13. 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,"<2010")

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

  15. Statistical Functions…continued • Enter the range address or the range name to select by in the Range text box. • Enter the condition expression or text in the Criteria text box. • Enter the range address or range name to average in the Average_range text box. • Click OK. Average_range text box

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

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

  18. 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")

  19. Math and Trigonometry Functions…continued To create a SUMIF formula: • Make the desired cell active. • Click the Formulas tab. • Click the Math & Trig button in the Function Library group. • Scroll down and click the SUMIFoption. continues on the next slide… SUMIF option

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

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

  22. Manage Range Names To edit a range name: • Click the Formulas tab. • Click the Name Manager button in the Defined Names group. • Click the desired range name. • Click the Edit button. continues on next slide… Edit button

  23. Manage Range Names…continued • Type a new range name in the Name text box. • Click OK. • Click Close. Name text box

  24. Manage Range Names…continued To delete a range name: • Click the Formulas tab. • Click the Name Manager button in the Defined Names group. • Click the desired range name. • Click the Delete button. • Click OK. • Click Close. Delete button

  25. 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).

  26. Lookup Functions…continued

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

  28. Lookup Functions…continued To create a VLOOKUP formula: • Make the desired cell active. • Click the Formulas tab. • Click the Lookup & Reference button in the Function Library group. • Click the VLOOKUP option. continues on next slide… VLOOKUP option

  29. Lookup Functions…continued • Enter the cell address, range name, or value in the Lookup_value text box. • Enter the range or range name in the Table_array text box. • Type the column number to return values from in the Col_index_num text box. • Type FALSE or leave blank for TRUE in the Range_lookup text box. • Click OK. Range_lookup text box

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

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

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

  33. Financial Functions…continued To create a PPMT formula: • Make the desired cell active. • Click the Formulas tab. • Click the Financial button in the Function Library group. • Click the PPMT option. continues on next slide… PPMT option

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

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

  36. Logical Functions…continued To create an IF Formula: • Make the desired cell active. • Click the Formulas tab. • Click the Logical button in the Function Library group. • Click the IF function. • Type the conditional test argument in the Logical_test text box. • Press Tab. • Type the argument in the Value_if_true text box. • Press tab. • Type the argument in the Value_if_false text box. • Click OK. Logical_test text box

  37. Logical Functions…continued

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

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

  40. Text Functions continues on next slide…

  41. Text Functions…continued

  42. Text Functions…continued To use the substitute text formula: • Make the desired cell active. • Type =SUBSTITUTE(. • Type the source text cell address. • Type a comma. • Type the text to be changed in the quotation symbols. • Type a comma. • Type the replacement text in quotation symbols. • Type ). • Press Enter. substitute text formula

  43. Text Functions…continued To convert text to uppercase: • Make the desired cell active. • Type =UPPER(. • Type the source cell address OR type the text to convert in quotation symbols. • Type ). • Press Enter. convert text to uppercase

  44. CHECKPOINT 2 • The Name Manager button is located in 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 • Replace existing text with new text using this function. • SUBSTITUTE • PROPER • UPPER • LOWER Answer Answer Next Question Next Slide

  45. 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 • Modify text using the text functions PROPER, UPPER, LOWER, and SUBSTITUTE

More Related