1 / 60

Microsoft Excel 2007 – Level 2

Chapter 2. Microsoft Excel 2007 – Level 2. ADVANCED FUNCTIONS AND FORMULAS. Use named ranges in formulas Use functions COUNTA, COUNTIF, COUNTIFS Use functions AVERAGEIF, AVERAGEIFS Use functions SUMIF, SUMIFS Delete a range name Look up data using the lookup functions VLOOKUP and HLOOKUP

britain
Download Presentation

Microsoft Excel 2007 – Level 2

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. Chapter 2 Microsoft Excel 2007 – Level 2 ADVANCED FUNCTIONS AND FORMULAS

  2. Use named ranges in formulas Use functions COUNTA, COUNTIF, COUNTIFS Use functions AVERAGEIF, AVERAGEIFS Use functions SUMIF, SUMIFS Delete a range name Look up data using the lookup functions VLOOKUP and HLOOKUP Analyze financial data using PPMT, PV and NPV Use conditional logic functions IF, AND, OR, NOT, and IFERROR Modify text using the text functions PROPER, UPPER, LOWER and SUBSTITUTE Performance Objectives

  3. Assigning a name to a range of cells allows you to reference a range of cells by a descriptive label A name should describe the range of cells for example, January_Sales 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 allowed, use underscore character or period to separate the words A valid cell address cannot become a range name Range names are not case sensitive Naming a Range

  4. Naming a Range…/2 Select the cell or range of cells to be named Click the Name box, type the name, and press Enter OR Click Type name and Click OK

  5. Using a Named Range in a Formula Range names can be used in formulas instead of the references to the cells Using a range name in a formula makes the formula easier to understand

  6. A function is a built-in formula Functions perform complex mathematical, financial, data-manipulation, and logical operations Functions include: The name of the function One or more arguments - (the data needed to perform the calculations or data manipulations, enclosed in parentheses) No spacebars unless part of a text string Text strings must be enclosed in quotations =function(argument1, argument2, etc.) Understanding Functions

  7. Entering a Function Functionscan be keyed directly into a cell OR Functions can be entered using the Insert Function button on the formula bar OR Click and choose the desired formula

  8. Insert Function Dialog Box Search for a function by typing a query OR Select a category and click the function A description of the currently selected function is displayed

  9. Function Arguments Dialog Box Entering arguments for the function … Click to select cellsin the worksheet If an argument name is bold, data must be entered in its box The result of the formula will display Click for Help Function Arguments return box appears and is filled in as you select cells Click to return

  10. Statistical Functions Excel's statistical functions are used on ranges of data

  11. Using the COUNT and COUNTA Functions Counts the number of cells containing numbers in a given range =COUNT(range) Counts the number of cells containingtext or a combination of text and numbersin a given range =COUNTA(range)

  12. Using the COUNT Function Make desired cell for result active Click Insert Function button Change category to Statistical Select ‘COUNT’, Click OK

  13. Using the COUNTA Function Make desired cell for result active Click Insert Function button Change category to Statistical Select COUNTA, Click OK

  14. COUNT and COUNTA Functions.../2 =COUNT(D8:D24) returns the incorrect result 0 =COUNTA(D8:D24) returns the correct result 17

  15. Using the COUNTIF Function Counts the number of cells in a given range that meet a specific condition =COUNTIF(range,criteria) The condition that must be met in order for that cell to be counted can be a number, anexpression, or text The range of cells to be counted

  16. Using the COUNTIF Function…/2 Make desired cell active Click Insert Function button Change category to Statistical Select COUNTIF, Click OK

  17. Using the COUNTIF Function…/3 Enter range name or address Type criteria Click OK Found 4 cells in the range A6:A20 that contained the word ‘Pathfinder’

  18. Using the COUNTIFS Function Counts the number of cells in a given range that meet multiple criteria The range of cells to be counted =COUNTIFS(range1,criteria1, range2,criteria2) The conditions that must be met

  19. Using the COUNTIFS Function…/2 COUNTIFS formula Multiple ranges and criteria to count Result of formula

  20. Using AVERAGEIF and AVERAGEIFS Functions Finds the Average within a specified range that meets a single criterion =AVERAGEIF(range, criteria,average_range) Finds the Average of cells with multiple criteria Conditions that must be met First range of cells to be tested First Range containing values to be averaged =AVERAGEIFS(average_range1, criteria1,average_range, range2,criteria2,average_range) Second set of arguments

  21. Using the AVERAGEIF Function Make desired cell active Click Insert Function button Change category to Statistical Select AVERAGEIF Click OK

  22. Using the AVERAGEIF Function…/2 Enter range to be tested Type criteria Enter range to be averaged Click OK Returns the average of those cells that meet the criteria

  23. Using the AVERAGEIFS Function Make desired cell active Click Insert function button Change category to Statistical Select AVERAGEIFS Click OK

  24. Using the AVERAGEIFs Function…/2 Enter range address or range name Enter range to scanand the criteria to meet Enter next range and condition Can enter up to 127 ranges and criteria; scroll to enter more Click OK

  25. Math and Trig Functions Excel includes many math and trigonometric functions to perform a wide variety of calculations

  26. Using the SUMIF Function Calculate the total of only those cells that meet a given condition or criteria =SUMIF(range,criteria,sum_range) the range of cells to be evaluated the range ofcells to sum • the condition or • criteria the cell is to • meet to be • included in the sum • can be a number, anexpression, or text

  27. Using the SUMIF Function…/2 Add only those commissions on sales greater than or equal to $40,000

  28. Using the SUMIFS Function Calculate the total of only those cells that meet multiple conditions or criteria the range ofcells to sum =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2,…) the range of cells to be evaluated the condition or criteria the cell is to meet to be included in the sum

  29. Managing Range Names Click Create a new name Select a name, then click to make changes or delete it

  30. Lookup and Reference Functions Excel's Lookup and Reference functions provide a way to extract information from a list

  31. Using the VLOOKUP Function Search for a value in the leftmost column of a table and return a value from a different column, from the same row as the value found the value to look up in the first column ofthe table =VLOOKUP(lookup_value,table_array,col_index_num[,range_lookup]) the lookup table or range of information the column number in the table that contains the value to be returned False for an exact matchTrue is default Brackets [ ] indicate an optional argument

  32. Using the VLOOKUP Function…/2 This function scans down the first column for the largest value that is less than or equal to the lookup value Table’s first column must be inascending order Use an absolute reference if you enter a cell address and plan to copy the formula

  33. Using the HLOOKUP Function Search for a value in the top row of a table and return a value from a different row, from the same column as the value found the value to look up in the first row ofthe table =HLOOKUP(lookup_value,table_array,row_index_num[,range_lookup]) the lookup table or range of information the row number in the table that contains the value to be returned False for an exact match Brackets [ ] indicate an optional argument

  34. Using the HLOOKUP Function…/2 Same as VLookup, except that the table is set up so the scan is horizontal from left to right Table’s first row must be inascending order,left to right

  35. Financial Functions Excel includes many financial functions used for calculating loan details, annuities, and investment analyses

  36. Present value – PV - the current value of amounts to be received or paid in the future discounted at some interest rate OR the amount that must be invested today at some interest rate to accumulate to some specified future value Number of periods – NPer - the number of payments that will be made to an investment or loan Payment – PMT - the amount paid or collected for each period Future value – Fv - the value of a loan or investment at the end of all the periods Rate - the interest rate being charged or paid Type - payments can either be made in arrears (at the end of each period) or in advance Arguments Used in Financial Functions

  37. Using the PMT Function Calculate the periodic payment of a loan based on constant payments and a constant interest rate =PMT(rate,nper,pv) interest rate per paymentperiod total number of payments to be made present value of the amount borrowed

  38. Using the PMT Function…/2 annual rate of interest must be divided by 12 for monthly payments the number of years times 12 months (5 x 12 = 60) cash you pay out is represented by a negative number

  39. Using the PPMT Function Calculate that portion of a loan payment used to reduce the principal, based on periodic, constant payments, and a constant interest rate =PPMT(rate,per,nper,pv,fv,type) in arrears or in advance interest rate per paymentperiod total number of payments to be made balance at the end of the loan- usually 0 period for which the principalpayment is made present value of the amount borrowed

  40. Using the PPMT Function…/2 annual rate of interest must be divided by 12 for monthly payments period of payment the number of years times 12 months (5 x 12 = 60) amount of money borrowed balance at theend of the loan-0 if blank

  41. PMT and PPMT Examples Compare two loans … =PMT(E4/12,E5*12,E6) =PMT(C4/12,C5*12,C6) =PPMT(E4/12,1,E5*12,E6) =PPMT(C4/12,1,C5*12,C6)

  42. Using the PV Function Calculate the present value that the total of a series of constant future payment amounts is worth today, assuming a constant interest rate over the life of the annuity =PV(rate,nper,pmt,fv,type) interest rate per period payment made each period in arrears or in advance total number of payments to be made future value

  43. Using the PV Function…/2 the Formula bar displays the function as it is being entered functions may include cell references

  44. Using the NPV Function Calculate the value of an investment today using a discount rate and incorporating a series of cash inflows or outflows related to the investment =NPV(rate,nper,pmt,fv,type) interest rate per period payment made each period in arrears or in advance total number of payments to be made future value

  45. Using the NPV Function…/2 the Formula bar displays the function entered

  46. Logical Functions Excel's logical functions perform logical tests to return a result, based on whether a condition is true or false

  47. Using the IF Function Set up a conditional statement to test data =IF(logical_test,value_if_true,value_if_false) condition that can be evaluated as being true or false that returned if the logical test is true that returned if the logical test is false

  48. Using the IF Function…/2 calculate commission, based on the Commission code You may enter values or cell references

  49. IF Function Operators

  50. Examples of IF Functions Simple conditions: =if(C6="FT","Salaried","Part-Time") =if(D6>50000,“High",“Low") Must be both full-time and make over $50,000: =if(and(C6=“FT”,D6>50000),“Bonus",“") May either be full-time or make over $50,000: =if(or(C6=“FT”,D6>50000),“Bonus",“") Must not be full-time: =if(not(C6=“FT”),“Bonus",“") Shows nothing if false

More Related