1 / 51

Exploring Advanced Functions

Exploring Advanced Functions. 6. Chapter. 6. 6-1 Rounding with Functions 6-2 Using SUMPRODUCT 6-3 Using SUMIF and SUMIFS 6-4 Using AVERAGEIF and AVERAGEIFS 6-5 Using COUNTIF and COUNTIFS 6-6 Finding the Middle Value with MEDIAN

seamus
Download Presentation

Exploring Advanced Functions

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. Exploring Advanced Functions 6 Chapter

  2. 6 6-1 Rounding with Functions 6-2 Using SUMPRODUCT 6-3 Using SUMIF and SUMIFS 6-4 Using AVERAGEIF and AVERAGEIFS 6-5 Using COUNTIF and COUNTIFS 6-6 Finding the Middle Value with MEDIAN 6-7 Finding the Most Common Value(s) with MODE.SNGL and MODE.MULT 6-8 Exploring More Logical Functions: AND, OR, and NOT 6-9 Creating Nested IF Formulas Exploring Advanced Functions

  3. 6 6-10 Calculating Future Value with the FV Function 6-11 Using PV to Calculate Present Value when Payments Are Constant 6-12 Using NPV to Calculate Present Value when Payments Are Variable 6-13 Calculating the Number of Payments with NPER 6-14 Creating a Depreciation Schedule 6-15 Finding Data with MATCH and INDEX 6-16 Using Database Functions 6-17 Managing Errors with the IFERROR Function 6-18 Analyzing Complex Formulas Using Evaluate Formula Exploring Advanced Functions

  4. Rounding with Functions • Rounding refers to adjusting a number up or down to make it more appropriate to the context in which it is being used. • ROUND — Rounds the number up or down to the number of decimal places specified in the Num_digits argument. =ROUND(Number,Num_digits) =ROUND(2500/503,2) Skill 6.1

  5. Rounding with Functions ROUNDUP — Ignores the rules of rounding and automatically rounds up to the next number, regardless of the value to the right of the rounding point. =ROUNDUP(Number,Num_digits) =ROUNDUP(2500/503,2)ROUND ROUNDDOWN — Ignores the rules of rounding and automatically rounds down, regardless of the value to the right of the rounding point. =ROUNDDOWN(Number,Num_digits) =ROUNDDOWN(2500/503,2) Skill 6.1

  6. Rounding with Functions To create a formula using one of the rounding functions: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the Math & Trig button. • Select the rounding function you want to open the Function Arguments dialog. • In the Number argument box, enter the number, cell reference, or formula you want rounded. • In the Num_digits argument box, enter the number of digits you want to the right of the decimal. • Click OK. Skill 6.1

  7. Calculating Totals with SUMPRODUCT SUMPRODUCT function allows you to multiply the corresponding cells in two or more ranges and then sum (add together) the products. =SUMPRODUCT(Array1,[Array2],[Array3]...) =SUMPRODUCT(Unit_Price,Units_Ordered) To create a formula using SUMPRODUCT: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the Math & Trig button. • Select SUMPRODUCT to open the Function Arguments dialog. • In the Array1 argument box, enter the first cell range or named range. • In the Array2 argument box, enter cell range or named range you want to multiply the values in Array1 by. • Continuing entering arrays as necessary. • Click OK. Skill 6.2

  8. Using SUMIF and SUMIFS SUMIF function totals the values only where cells meet the specified criteria. SUMIF takes two required arguments and one optional argument: Range: the range of cells to evaluate against the criteria Criteria: the conditions (the criteria) the cell must meet in order to be included in the total, and optionally Sum_range(optional): the range of cells containing the values to be summed. Skill 6.3

  9. Using SUMIF and SUMIFS To create a formula using SUMIFS: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the Math & Trig button. • Select SUMIF to open the Function Arguments dialog. • In the Range argument box, enter the cell range or named range to evaluate against the criteria. • In the Criteria argument box, enter the criteria. The criteria can be a text string, numerical value, expression, or cell reference. • In the Sum_range argument box, enter the cell range or named range containing the values you want to add together. If you omit this argument, SUMIF will total the values in the Range argument instead. • Click OK. Skill 6.3

  10. Using SUMIF and SUMIFS SUMIFS takes arguments for up to 127 pairs of criteria ranges and criteria. Sum_range: the range of cells to sum where criteria are met Criteria_range1: the range of cells containing the values to be evaluated against the first criteria Criteria1: the first criteria Criteria_range2 (optional): the range of cells containing the values to be evaluated against the second criteria Criteria2 (optional):the second criteria (continuing up to Criteria_range127 and Criteria127) Skill 6.3

  11. Using SUMIF and SUMIFS To create a formula using SUMIFS: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the Math & Trig button. • Select SUMIFS to open the Function Arguments dialog. • In the Sum_range argument box, enter the cell range or range name containing the values to be summed if all the criteria are met. • In the Criteria1_range box, enter the cell range or range name containing the values to be evaluated against the first criteria. • In the Criteria1 argument box, enter text string, number, expression, or cell reference for the first criteria. • In the Criteria2_range box, enter the cell range or range name containing the values to be evaluated against the second criteria. • In the Criteria2 argument box, enter text string, number, expression, or cell reference for the second criteria. • Continue entering criteria range and criteria pairs until you are finished. • Click OK. Skill 6.3

  12. Using AVERAGEIF and AVERAGEIFS AVERAGEIFfunction totals the values only where cells meet the specified criteria. AVERAGEIFtakes two required arguments and one optional argument: Range: the range of cells to evaluate against the criteria Criteria: the conditions (the criteria) the cell must meet in order to be included in the total, and optionally Average_range(optional): the range of cells containing the values to be averaged. Skill 6.4

  13. Using AVERAGEIF and AVERAGEIFS To create a formula using AVERAGEIF: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select AVERAGEIF to open the Function Arguments dialog. • In the Range argument box, enter the cell range or named range to evaluate against the criteria. • In the Criteria argument box, enter the criteria. The criteria can be a text string, numerical value, expression, or cell reference. Remember, text strings and expressions must be enclosed in quotation marks. • In the Average_range argument box, enter the cell range or named range containing the values you want to average. If you omit this argument, AVERAGE will average the values in the Range argument instead. • Click OK. Skill 6.4

  14. Using AVERAGEIF and AVERAGEIFS AVERAGEIFStakes arguments for up to 127 pairs of criteria ranges and criteria. Average_range: the range of cells to average where criteria are met Criteria_range1: the range of cells containing the values to be evaluated against the first criteria Criteria1: the first criteria Criteria_range2 (optional): the range of cells containing the values to be evaluated against the second criteria Criteria2 (optional):the second criteria (continuing up to Criteria_range127 and Criteria127) Skill 6.4

  15. Using AVERAGEIF and AVERAGEIFS To create a formula using AVERAGEIFS: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select AVERAGEIFS to open the Function Arguments dialog. • In the Average_range argument box, enter the cell range or range name containing the values to be averaged if all the criteria are met. • In the Criteria1_range box, enter the cell range or range name containing the values to be evaluated against the first criteria. • In the Criteria1 argument box, enter text string, number, expression, or cell reference for the first criteria. Remember, text strings and expressions must be enclosed in quotation marks. • In the Criteria2_range box, enter the cell range or range name containing the values to be evaluated against the second criteria. • In the Criteria2 argument box, enter text string, number, expression, or cell reference for the second criteria. Remember, text strings and expressions must be enclosed in quotation marks. • Continue entering criteria range and criteria pairs until you are finished. • Click OK. Skill 6.4

  16. Using COUNTIF and COUNTIFS There are also counting functions that allow you to count only data that meet specific criteria: COUNTIF and COUNTIFS COUNTIF takes two arguments: Range: The range of cells to count Criteria: The conditions (the criteria) the cell must meet in order to be counted. The criteria can be a text string or numerical value or expression =COUNTIF(Range,Criteria)=COUNTIF(Instock,"<5") Skill 6.5

  17. Using COUNTIF and COUNTIFS To create a formula using COUNTIF: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select COUNTIF to open the Function Arguments dialog. • In the Range argument box, enter the cell range or named range to evaluate against the criteria. • In the Criteria argument box, enter the criteria. The criteria can be a text string, numerical value, expression, or cell reference. Remember, text strings and expressions must be enclosed in quotation marks. • Click OK. Skill 6.5

  18. Using COUNTIF and COUNTIFS COUNTIFStakes arguments for up to 127 pairs of criteria ranges and criteria. COUNTIFS takes two required arguments and multiple optional arguments: Criteria_range1: the range of cells containing the values to be evaluated against the first criteria Criteria1: the first criteria Criteria_range2 (optional): the range of cells containing the values to be evaluated against the second criteria Criteria2 (optional): the second criteria (continuing up to Criteria_range127 and Criteria127) Skill 6.5

  19. Using COUNTIF and COUNTIFS To create a formula using COUNTIFS: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and select COUNTIFS to open the Function Arguments dialog. • In the Criteria1_range box, enter the cell range or range name containing the values to be evaluated against the first criteria. • In the Criteria1 argument box, enter text string, number, expression, or cell reference for the first criteria. • In the Criteria2_range box, enter the cell range or range name containing the values to be evaluated against the second criteria. • In the Criteria2 argument box, enter text string, number, expression, or cell reference for the second criteria. • Continue entering criteria range and criteria pairs until you are finished. • Click OK. Skill 6.5

  20. Finding the Middle Value Medianis the middle value of a set of values. =MEDIAN(Number1,[Number2]...)=MEDIAN(B2:B12) If you prefer using the Function Arguments dialog, on the Formulas tab, in the Function Library group, click the More Functions button, point to Statistical, and then click MEDIAN to open the Function Arguments dialog. MEDIAN takes up to 255 Number arguments. Skill 6.6

  21. Finding the Most Common Value(s) with MODE.SNGL and MODE.MULT The statistical mode is the value that appears most often in a group of values. =MODE.SNGL(Number1,[Number2]...)=MODE.SNGL(B1:B12) Skill 6.7

  22. Finding the Most Common Value(s) with MODE.SNGL and MODE.MULT If your dataset includes more than one mode, you can use the MODE.MULT function in an array formula to return multiple values. =MODE.MULT(Number1,[Number2]...)=MODE.MULT(B1:B12) Skill 6.7

  23. Exploring More Logical Functions: AND, OR, and NOT • If you are looking for a true value to multiple conditions, use AND. • If you are looking for a true value to any one of multiple conditions, use OR. • If you are looking for a true value to a question posed in the negative, use NOT. The logical function AND returns TRUE if all the arguments are true, and FALSE if at least one of the arguments is false. =AND(Logical1,[Logical2]...)=AND(D4<=E4) Skill 6.8

  24. Creating Nested IF Formulas The figure to the right translates the logic flow diagram into a nested IF statement with three conditions to evaluate: • The first logical test is the outermost IF statement. If the value in the Date Expected column is greater than the value of the cell named CompareDate, then display "order pending". • If not, then evaluate if the value in the Ordered column is equal to the value in the Received column. If true, display "order closed". • If not, then evaluate if too few items were received. This is the last function in the logic flow—the innermost IF statement. If the value in the Received column is less than the value in the Ordered column, display "contact vendor". If not (in other words, if too many were received), display "return excess". Skill 6.9

  25. Creating Nested IF Formulas =IF(E4>CompareDate,"order pending",IF(F4=G4,"order closed",IF(F4<G4,"contact vendor","return excess"))) The easiest way to build a nested IF formula is to use the Function Arguments dialog. • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the Logical button and select IF. • In the Logical_test box, enter the condition you want to test for in the outermost IF function. • You could type the next IF functions directly in the Value_if_True box or the Value_if_False box, but it is easier to open another Function Arguments dialog. • Click in the argument box where you want the next IF statement, and then click the arrow in the Name box to the left of the formula bar to display the list of most recently used functions. If the function you want is not listed (IF), click More Functions at the bottom of the list to open the Insert Function dialog from which you can open the Function Arguments dialog for any function. Skill 6.9

  26. Creating Nested IF Formulas • A new Function Arguments dialog opens. The previous dialog is still open in the computer's memory, but now you have an empty dialog in which to enter the next level of the nested IF formula. Enter the new Logical_test, Value_if_true, and Value_if_false arguments. Repeat Step 5 to include another nested IF statement as one of the arguments in this dialog. • You can repeat steps 5-6 as many times as you need to build the nested formula. • When you have entered the arguments for the innermost IF statement, click OK to return to the previous Function Arguments dialog. Continue clicking OK until you reach the outermost IF statement dialog, and then click OK to close the Function Arguments dialog and enter the formula into your workbook. Skill 6.9

  27. Calculating Future Value with the FV Function FV function is used to calculate the future value of an investment. For example, if you deposit the same amount of money every period at a steady interest rate, use FV to calculate the future value of your savings. • Rate—the interest rate. • Nper—the total number of payments. • Pmt—the amount of each payment. • Pv—(optional) the present value or how much the investment is worth today. • Type—(optional) enter 1 for this argument if the payment is made at the beginning of each period. Skill 6.10

  28. Calculating Future Value with the FV Function To create a formula using FV: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the Financial button. Click FV to open the Function Arguments dialog. • Enter the arguments. In this example, the interest rate is annual and the payments are monthly, so the interest rate must be divided by 12. Notice that the Pmt argument includes a hyphen before the cell reference to make it negative. The Pv and Type arguments are optional and not necessary in this example. • Click OK Skill 6.10

  29. Calculating Future Value with the PV Function PV (present value) is the value today of a series of future payments. For example, at today's interest rates, what is the lump sum you would need to fund a retirement account to pay an annuity (a series of constant payments) to yourself? • Rate—the interest rate. • Nper—the total number of payments. • Pmt—the amount of each payment. • Fv—(optional) the amount of money left after the last payment is made (the future value of the investment). • Type—(optional) enter 1 for this argument if the payment is made at the beginning of each period. Skill 6.11

  30. Calculating Future Value with the PV Function To create a formula using PV: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the Financial button. Click PV to open the Function Arguments dialog. • Enter the arguments. In our example, the payments will be monthly, so the annual interest rate (Rate) must be divided by 12 and the number of payout years (Nper) must be multiplied by twelve. The Pmt argument includes a hyphen before the cell reference to make it negative. The Pv argument is omitted, but the Type argument is set to 1 because payments will be made at the beginning of every period. • Click OK Skill 6.11

  31. Using NPV to Calculate Present Value when Payments Are Variable The NPV function is similar to PV, but it allows for variable payment amounts when calculating the present value of future payments. NPV can be especially useful for comparing two investment or payment options. The NPV function has two required arguments: • Rate—the discount rate—the interest rate which would be reasonably expected if the money were invested elsewhere. • Value—the payment amount both incoming (positive) and outgoing (negative). NPV takes up to 254 Value arguments (Value1, Value2, etc.). Skill 6.12

  32. Using NPV to Calculate Present Value when Payments Are Variable To use the NPV function: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the Financial button. Click NPV to open the Function Arguments dialog. • In the Rate box, enter the interest rate. The NPV Function Arguments dialog calls this the discount rate. When working with NPV, the discount rate is the same as the interest rate—just different terminology. • The remaining arguments are the payments (negative values) and income (positive values). NPV accepts up to 254 Value arguments. You can use cell ranges, named ranges, or individual cell references. If you use a cell range or named range, verify that they do not include blank cells—they will be ignored and the NPV calculation will be wrong. • Click OK Skill 6.12

  33. Calculating the Number of Payments with NPER NPER function is used to calculate the number of payments available from a retirement account or due on a loan given a constant interest rate and payment amount. The NPER function has three required arguments and two optional arguments: Rate—the interest rate. Pmt—the amount of each payment. Pv—the value of the investment or loan today. Fv—(optional) the amount of money left after the last payment is made (the future value). Type—(optional) enter 1 for this argument if the payment is made at the beginning of each period. Skill 6.13

  34. Calculating the Number of Payments with NPER To create a formula using NPER: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the Financial button. Click NPER to open the Function Arguments dialog. • Enter the arguments. In our example, the payments will be monthly, so the annual interest rate (Rate) must be divided by 12. The Pmt argument includes a hyphen before the cell reference to make it negative. The Fv argument is omitted, but the Type argument is set to 1 because payments will be made at the beginning of every period. • Click OK Skill 6.13

  35. Creating a Depreciation Schedule Depreciation is when a business purchases a significant asset such as equipment or computer software, generally accepted accounting principles (GAAP) state that the expense must be spread over the asset's useful lifetime. • Depreciation over the life of an asset is figured in a depreciation schedule. • At the end of each accounting period, the asset's book value is recalculated using the initial cost minus the accumulated depreciation. Skill 6.14

  36. Creating a Depreciation Schedule There are multiple ways to calculate depreciation, but most depreciation functions use the same arguments: • Cost—the initial cost of the asset—usually the purchase price. • Salvage—the fair market value of the asset at the end of its useful life. • Life—the number of years the asset will be used. • Pay or Period—the number of the accounting period, where each accounting period is one year of the asset's life. Types of Depreciation • Straight-line • Accelerated • Declining Balance (DB) • Double-Declining Balance (DDB) Skill 6.14

  37. Creating a Depreciation Schedule • Straight-line depreciation is the simplest depreciation to figure: (the cost of the asset minus the salvage value) divided by the life of the asset (in years). To calculate straight-line depreciation, use the SLN function. =SLN(Cost,Salvage,Life) • Accelerated depreciation assumes that the asset is worth more at the beginning of its lifespan and uses a higher rate of depreciation for early years in the depreciation schedule. Use the SYD function to calculate SOYD depreciation. =SYD(Cost,Salvage,Life,Per)=SYD(Cost,Salvage,Life,A5) Skill 6.14

  38. Creating a Depreciation Schedule • Declining balance depreciation takes into account the declining book value of the asset. Use the DB function to calculate declining balance depreciation. =DB(Cost,Salvage,Life,Period,[Month])=DB(Cost,Salvage,Life,A5,6) • Double-Declining balance depreciation is similar to declining balance depreciation, but it uses a multiplier (usually 2) to accelerate the depreciation. =DDB(Cost,Salvage,Life,Period,[Factor])=DDB(Cost,Salvage,Life,A5,1.5) Skill 6.14

  39. Finding Data with MATCH and INDEX MATCH returns the position of a specific value in a single row or column array. INDEXreturns the value at the intersection of a specified row and column in an array. First use MATCH to find the row or column position of the value you want, and then use the results of MATCH as the row number or column number argument in INDEX. The MATCH function has two required arguments and one optional argument: • Lookup_value—the text, number, or logical value you want to match. • Lookup_array—the range of cells grouped in a single row or column that contains the value you want to look up. • Match_type—(optional) If you want to return only an exact match, you must enter 0 as the argument value. If the lookup array is sorted from smallest to largest, enter 1 (or omit the argument) to find the first position where the value is less than or equal to the lookup value. If the array is sorted from largest to smallest, enter -1 to find the first position where the value is greater than or equal to the lookup value. Skill 6.15

  40. Finding Data with MATCH and INDEX To create a formula using MATCH: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the Lookup & Reference button, and select MATCH to open the Function Arguments dialog. • Enter the Lookup_value argument. The example in Figure EX 6.48 allows the workbook user to enter an item name in cell B1. The Lookup_value argument then references that cell as shown in Figure EX 6.49. • Enter the Lookup_array argument. In Figure EX 6.49, this argument uses a named range that refers to a vertical range of cells. • If necessary, enter the Match_type argument box. In this example, we want to ensure an exact match, so 0 is entered as the Match_type argument. • Click OK. Skill 6.15

  41. Finding Data with MATCH and INDEX The INDEX function in this example has three required arguments: • Array—the range of cells containing the entire data array. If your data include a header row, do not include it in the cell range or named range used for the Array argument. • Row_num—the row position in the array for the value you want to look up. • Column_num—the column position in the array for the value you want to look up. =INDEX(Array, Row_num,Column_num) =INDEX(Inventory,B2,3) Skill 6.15

  42. Finding Data with MATCH and INDEX To create a formula using INDEX: • Select the cell where you want to enter the formula. • On the Formulas tab, in the Function Library group, click the Lookup & Reference button, and select INDEX to open the Function Arguments dialog. • There are two possible arguments lists for the INDEX function. Select the array,row_num,column_num option. Click OK. • In the Array box, enter the range of cells or the name for the entire data array. • In the Row_num box, enter the reference to the cell that contains the row position you want to look up in the array. In this example, that is the cell containing the MATCH formula. • In the Column_num box, enter the number of the column that contains the data you want displayed in the formula results. • Click OK Skill 6.15

  43. Using Database Functions Database functions allow you to perform statistical analysis on data that meet specific criteria by building queries similar to those used when working with a database. DSUM, DAVERAGE, DMIN, DMAX, DCOUNT, DCOUNTA • Database—the data arranged in rows and columns with column labels. • Field—the column to use in the calculation. The column can be identified by the column number in the database array or by the column label enclosed in quotation marks. • Criteria—the cell range defining the conditions the data must meet in order to be included in the calculation. The criteria range must have at least two rows. The first row includes labels that match the column labels in the database range. You need only include the columns for which you will enter criteria. The second row is where you enter the criteria. Criteria can be numeric values, expressions, or text. Skill 6.16

  44. Using Database Functions To create a formula using one of the database functions: • First, set up the criteria range. If you place the criteria range above the database range, include a blank row between the two ranges. • Enter the criteria in the blank row below the appropriate column heading. • After setting up the criteria range, select the cell where you want to enter the formula. On the Formulas tab, in the Function Library group, click the Insert Function button to open the Insert Function dialog. (Database functions are not accessible from any of the Ribbon buttons.) • If necessary, expand the Or select a category list and select Database. • Select the function you want, and then click OK to open the Function Arguments dialog. • In the Database box, enter the range of cells or the name for the data array. Include the heading row. • In the Field box, enter the column number or the column label of the column containing the data to use in the calculation. If you use the column. • In the Criteria box, enter the range of cells or the name for the criteria range. • Click OK. Skill 6.16

  45. Using Database Functions Skill 6.16

  46. Managing Errors with the IFERROR Function IFERROR function: Rather than displaying an error message to users, it may be more useful to display a text message or a specific value that you define. By adding the IFERROR function around the existing VLOOKUP function, we can make the error message more user-friendly. The IFERROR function takes two arguments. • Value—the formula to calculate. • Value_if_error—the value, text string, or formula to use if the formula in the Value argument results in an error. If you use a text string, enclose it in quotation marks. Skill 6.17

  47. Managing Errors with the IFERROR Function To use the Function Arguments dialog to create a formula using IFERROR: • On the Formulas tab, in the Function Library group, click the Logical button, and select IFERROR. • In the Value box, enter the formula you want to calculate. • In the Value_if_error box, enter the text string, value, or formula the IFERROR formula should use if Excel is unable to compute a value for the formula in the Value argument. • Click OK Skill 6.17

  48. Analyzing Complex Formulas Using Evaluate Formula To use the Evaluate Formula feature: • To evaluate a specific formula, click the cell that contains the formula you want to review. • On the Formulas tab, in the Formula Auditing group, click the Evaluate Formula button to open the Evaluate Formula dialog for the selected cell. • Click the Step In button to see the referenced cell highlighted in the worksheet so you can review the precedent formula. Skill 6.18

  49. Analyzing Complex Formulas Using Evaluate Formula To use the Evaluate Formula feature: • If the referenced cell contains a reference to another cell, you can click Step In again. The previous formula is still shown in the dialog to help you follow the trail that leads back to the formula in the cell you originally selected. • Continue clicking Step In to review the layers of precedent cells. When you have reached the end, the Step In button will be disabled. Click the Step Out button to remove the highlighting and go back one level. Skill 6.18

  50. Analyzing Complex Formulas Using Evaluate Formula To use the Evaluate Formula feature: • At any point, you can click the Evaluate button to see the current value for the underlined part of the current formula. Continue clicking Evaluate until you have reviewed each part of the formula and the final result is displayed in the Evaluation box. If you reach a part of the formula that Excel cannot compute, the Evaluation box will display an error message instead of a value. • Continue clicking Step Out and Evaluate until you have examined all parts of the formula, at which point you can click the Start Over button to begin the evaluation process from the beginning. • Click the Close button to dismiss the Evaluate Formula dialog Skill 6.18

More Related