Perform calculations on data In this chapter Name groups of data Create formulas to calculate values Summarize data that meets specific conditions Set iterative calculation options and enable or disable automatic calculation Use array formulas Find and correct errors in calculations Excel 2019 workbooks give you a handy place to store and organize your data, but you can do a lot more with your data in Excel than just store it. One important task you can perform is to calculate totals for values in a series of related cells. You can also use Excel to discover other information about data you select, such as the maximum or minimum value in a group of cells. And if you make an error, you can find the cause and correct it quickly. Often, you can't access the information you want without referencing more than one cell, and it’s also often true that you'll use the data in the same group of cells for more than one calculation. Excel makes it easy to reference several cells at the same time, so you can define your calculations quickly.This chapter guides you through procedures related to streamlining references to groups of data on your worksheets and creating and correcting formulas that summarize an organization's business operations. Name groups of data When you work with large amounts of data, it's often useful to identify groups of cells that contain related data. For example, you can create a worksheet in which columns of cells contain data summarizing the number of packages handled during a specific time period and each row represents a region. A 8 € o E F 6 H i 1 2 3 5:00PM 6:00PM _7-00PM 8:00PM 8:00PM 10:00 PM_11:00 Ph] 4 [Northeast 53.587 41,438 36599 <43,023-<97,664 44,030 36,930 5 latlantic 290 19407, «9,209 10,707, «11,277 «10,780 14838 6 \Southeast 7207 13475 13,589 14,702 7,769 10,979 10,919 7 INorth Central | 9,829 9.959 10.367 8.062 14ea7 12085 «8,015 8 Midwest 7397 7,811 10.292 7,776 «14805 «8777 14,480 9 Southwest 7738 14352 «7,222«112_—sdoaa— 10,686 14,7 10 Mountain West] 9,721 8404 «11944 = Bt62 14531 11,348 B.559 ct Northwest 9240 10995 7836 9,702 9,265. 14,209,798 2 central 11gi0 13625 6921135931042 10223 —13,338, 8 Worksheets often contain logical groups of data Instead of specifying the cells individually every time you want to use the data they contain, you can define those cells as a range (also called a named range). For example, you could group the hourly packages handled in the Northeast region shown in the preceding image into a group called NortheastVolume. Whenever you want to use the contents of that range in a calculation, you can use the name of the range instead of specifying the range’s address.S Tip Yes, you could just name the range Northeast, but if you use the range’s values in a formula in another worksheet, the more descriptive range name tells you and your colleagues exactly what data is used in the calculation. If the cells you want to define as a named range have labels in a row or column that’s part of the cell group, you can use those labels as the names of the named ranges. For example, if your data appears in worksheet cells B4:112 and the values in column B are the row labels, you can make each row its own named range. A B G 1 2 3 5:00 PM 4 Northeast 53,587 5 Atlantic 8,896 6 Southeast 7,207 7 North Central 9,829 8 Midwest 7,397 9 Southwest 7,735 10 Mountain West| 9,721 " Northwest 9,240 12 Central 11,810 | Select a group of cells to create a named range If you want to manage the named ranges in your workbook—for example, to edit a range’s settings or delete a range you no longer need—you can do so in the Name Manager dialog box.Name Manager {11.810 73975 78117) 10. |BEimountain.west. (972177 8404"7 11. |[Blnonh.cenval (9820779950 10. | Blnomneas (9388777 41438" | Blinorthwest (¢9240"7 10995 +7. | Bsounease (720777 13475771 Refers to: XK Sheet] !$C95155 (777357 11352" T= Edt. Delete SheetT!$C$12Si81, ~SheettI$cS8sis8 =SheetT/$C$1051510 =SheettI$CS7-8'87 =Sheeth $CS4:8'54 Sheet !$C$11:$1817 =Sheett'SCS6$'$6 =Sheett/$CS9:8i$9 ‘Manage named ranges in the Name Manager dialog box Scope ‘Workbook Workbook Workbook Workbook Workbook Workbook Workbook Workbook Biter” Close x A Gr If your workbook contains a lot of named ranges, you can select the Filter button in the Name Manager dialog box and select a criterion to limit the names displayed in the dialog box. To create a named range 1. Select the cells you want to include in the named range. 2. In the Name Box, next to the formula bar, enter a name for your named range. Or1. Select the cells you want to include in the named range. 2. On the Formulas tab of the ribbon, in the Defined Names group, select Define Name. 3. In the New Name dialog box, enter a name for the named range. 4. Verify that the named range includes the cells you want. 5. Select OK. To create a series of named ranges from worksheet data with headings 1. Select the cells that contain the headings and data you want to include in the named ranges. 2. Inthe Defined Names group, select Create from Selection. 3. Inthe Create Names from Selection dialog box, select the check box next to the location of the heading text from which you want to create the range names. 4. Select OK. To edit a named range 1. Inthe Defined Names group, select Name Manager. 2. Select the named range you want to edit. 3. In the Refers to box, change the cells to which the named range refers. Or Select Edit, edit the named range in the Edit Range box, and select OK. 4. Select Close. To delete a named range 1. Select Name Manager. 2. Select the named range you want to delete. 3. Select Delete.4. Select Close. Create formulas to calculate values After you add your data to a worksheet and define ranges to simplify data references, you can create a formula, which is an expression that performs calculations on your data. For example, you can calculate the total cost of a customer's shipments, figure the average number of packages for all Wednesdays in the month of January, or find the highest and lowest daily package volumes for a week, month, or year. To enter an Excel formula into a cell, you start with an equal sign (=). Excel then knows that the expression that follows should be interpreted as a calculation, not text. After the equal sign, you enter the formula. For example, you can find the sum of the numbers in cells C2 and C3 by using the formula =C2+C3. After you have entered a formula into a cell, you can revise it by selecting the cell and then editing the formula in the formula bar. For example, you can change the preceding formula to =C3-C2, which calculates the difference between the contents of cells C2 and C3. A IMPORTANT If Excel treats your formula as text, make sure you haven't accidentally put a space before the equal sign. Remember, the equal sign must be the first character! Entering the cell references for 15 or 20 cells in a calculation would be tedious, but in Excel you can easily enter complex calculations by using the Insert Function dialog box. The Insert Function dialog box includes a list of functions, or predefined formulas, from which you can choose.Insert Function 2 x Search fora funtion: {ype abe deze of what ou want te de ad tien GickGo @ COcselestasategons Mest Recently Used Selet«funcion LT - -AVERAL ‘ HYPERLINK count SUM (number number.) Addl al the numbersin a range of ells pon is tuseton 0K Cancel Create formulas in the Insert Function dialog box we following table describes some of the most useful functions in the ist. Function Description SUM Finds the sum of the numbers in the specified cells AVERAGE Finds the average of the numbers in the specified cells COUNT Finds the number of entries in the specified cells MAX Finds the largest value in the specified cells MIN Finds the smallest value in the specified cells Two other functions you might use are the NOW and PMT functions. The NOW function displays the time at which Excel updated the workbook's formulas, so the value will change every time the workbook recalculates. The proper form for this function is =NOW().You could, for example, use the NOW function to calculate the elapsed time from when you started a process to the present time. The PMT function is a bit more complex. It calculates payments due on a loan, assuming a constant interest rate and constant payments. To perform its calculations, the PMT function requires an interest rate, the number of payments, and the starting balance. The elements to be entered into the function are called arguments and must be entered in a certain order. That order is written as PMT(rate, nper, py, fv, type) The following table summarizes the arguments in the PMT function. Argument Description rate The interest rate, to be divided by 12 for a loan with monthly payments, by 4 for quarterly payments, and so on nper The total number of payments for the loan pv The amount loaned (pv is short for present value, or principal) fv The amount to be left over at the end of the payment cycle (usually left blank, which indicates 0) type 0 or 1, indicating whether payments are made at the beginning or end of the month (usually left blank, which indicates 0, or the end of the month) For example, if a company wanted to borrow $2,000,000 at a 6 percent interest rate and pay the loan back over 24 months, you could use the PMT function to figure out the monthly payments. In this case, you would write the function =PMT(6%/12, 24, 2000000), which calculates a monthly payment of $88,641. 22.S Tip The 6-percent interest rate is divided by 12 because the loan’s interest is compounded monthly. You can also use the names of any ranges you have defined to supply values for a formula. For example, if the named range NortheastLastDay refers to cells C4:14, you can calculate the average of cells C4:14 with the formula =AVERAGE(NortheastLastDay). With Excel, you can add functions, named ranges, and table references to your formulas more efficiently by using the application's Formula AutoComplete capability. Just as AutoComplete offers to fill in a cell's text value when Excel recognizes that the value you're typing matches a previous entry, Formula AutoComplete offers to help you fill in a function, named range, or table reference while you create a formula. © SEE ALSO For more information on defining Excel tables, see the appendix of this book. Consider a worksheet that contains a two-column Excel table named Exceptions. The first column is labeled Route; the second is labeled Count. You refer to the table by typing the table name, followed by the column or row name in brackets. For example, the table reference Exceptions[Count] would refer to the Exceptions table's Count column.1 2 101 4 3 102 9 4 103 12 5 104 8 6 105 5 Z 106 6. 8 107 6 9 108 5 10 109 14 u 110 Zz, 12 Excel tables track data in a structured format To create a formula that finds the total number of exceptions by using the SUM function, you begin by typing =SU. When you enter the letter S, Formula AutoComplete lists functions that begin with the letter S; when you enter the letter U, it narrows the list down to the functions that start with the letters SU. Excel displays Formula AutoComplete suggestions to help with formula creation To add the SUM function to the formula, select SUM and then press Tab. The function appears in the formula bar followed by an open parenthesis. To begin adding the table reference, enter the letter E. Excel displays a list of available functions, tables, and named ranges that start with the letter E. Select Exceptions, and press Tab to addthe table reference to the formula. Then, because you want to summarize the values in the table’s Count column, enter an opening bracket, and, in the list of available table items, select Count. Finally, enter a closing bracket followed by a closing parenthesis to finish creating the formula =SUM(Exceptions[Count]). If you want to include a series of contiguous cells in a formula, but you haven't defined the cells as a named range, you can select the first cell in the range and drag to the last cell. If the cells aren't contiguous, hold down the Ctrl key and select all the cells to be included. In both cases, when you release the mouse button, references to the cells you selected appear in the formula. A 3 c en a TE 3 sod ‘ tatcivn | Mate ; Chon dive [STHAbB0] 7 foringatle $6500 : Sent! 5 isszeco 3 r Long Dk i Conte 5 210900 12 taber 5 As0000 a 3 “aca 14 Exematon [300 ows Sat 5 1303800 » 2 saidTonl $ sisasc0 a Labor Percentage ” wowsot 22 A SUM formula that adds individual cells instead of a continuous range In addition to using the Ctrl key to add cells to a selection, you can expand a selection by using a wide range of keyboard shortcuts. The following table summarizes many of these shortcuts. Key sequence Description Shift+Right Arrow Extend the selection one cell to the right. Shift+Left Arrow Extend the selection one cell to the left.Key sequence Shift+Up Arrow ShifttDown Arrow Ctrl+Shift+Right Arrow Ctrl+Shift+Left Arrow Ctrl+Shift+Up Arrow Ctrl+Shift+Down Arrow Ctrl+Shift+8 (Ctr+*) Shiftt Home Ctrl+Shift+ Home Ctrl+Shift+End Shift+ PageDown Shift+PageUp Alt; Description Extend the selection up one cell. Extend the selection down one cell. Extend the selection to the last non-blank cell in the row. Extend the selection to the first non-blank cell in the row. Extend the selection to the first non-blank cell in the column. Extend the selection to the last non-blank cell in the column. Select the entire active region. Extend the selection to the beginning of the row. Extend the selection to the beginning of the worksheet. Extend the selection to the end of the worksheet. Extend the selection down one screen. Extend the selection up one screen. Select the visible cells in the current selection. After you create a formula, you can copy it and paste it into another cell. When you do, Excel changes the formula to work in the new cells. For instance, suppose you have a worksheet in which cell D8 contains the formula =SUM(C2:C6). If you select cell D8, copy the cell's contents, and then paste the result into cell D16, Excel writes=SUM(C10:C14) into cell D16. In other words, it reinterprets the formula so it fits the surrounding cells! Excel knows to reinterpret the cells used in the formula because the formula uses a relative reference, or a reference that can change if the formula is copied to another cell. Relative references are written with just the cell row and column—for example, C14. Relative references are useful when you summarize rows of data and want to use the same formula for each row. As an example, suppose you have a worksheet with two columns of data, labeled Sale Price and Rate, and you want to calculate a sales representative's commission by multiplying the two values in a row. To calculate the commission for the first sale, you would enter the formula =A2*B2 in cell C2 ao oo Calinr [1 Aa == y |B Ulla a Cipbows fore 2 - fe A 3 c D Sale Price Rate Commission 1 2|$ 19,720 6%| $ 1,183.20 | 3 $11,287 6% 43 18926 6% 5 $ 5,722 6% 6 $ 15,785 6% 7 $ 10,445 6% 8 Use formulas to calculate values such as commissions Selecting cell C2 and dragging the fill handle until it covers cells C2:C7 copies the formula from cell C2 into each of the other cells. Because you created the formula by using relative references, Excel updateseach cell's formula to reflect its position relative to the starting cell (in this case, cell C2). The formula in cell C7, for example, is =A7*B7. ee oe Gaibi =| tt y BIU- lipboard Font > Alignmen 7 - Se =A7*B7 A 8 c D 1 Sale Price Rate Commission 2 $ 19,720 6% $ 1,183.20 30 $ 11,287 6% $ 677.22 4 $ 18,926 6% $ 1,135.56 5 $ 5,722 6% $ 343.32 6 S$ 15,785 6% $947.10 7|$ 10,445 6%[$ 626.70 8 iA Copying formulas to other cells to summarize additional data You can use a similar technique when you add a formula to an Excel table column. For example, suppose the sale price and rate data were in an Excel table, and you created the formula =A2*B2 in cell C2. Excel would apply the formula to every other cell in the column. Because you used relative references in the formula, the formulas would change to reflect each cell's distance from the original cell.c D 1 ed ~ 2S 19,720 6% $ 1,183.20 3[$s 11,287 6%] $ 677.22 4$ 18,926 6% $ 1135.56 | 5 $ 6% $ 343.32 6\$ 6% $ 947.10 Za S 6% $ 626.70 Adding a formula to an Excel table cell to calculate values in a column If you want a cell reference to remain constant when you copy the formula that is using it to another cell, you can use an absolute reference. To write a cell reference as an absolute reference, you enter $ before the row letter and the column number. For example, if you want the formula in cell D16 to show the sum of values in cells C10 through C14 regardless of the cell into which it is pasted, you can write the formula as =SUM($C$10:$C$14). G Tip Another way to ensure that your cell references don't change when you copy a formula to another cell is to select the cell that contains the formula, copy the formula’s text in the formula bar, press the Esc key to exit cut-and-copy mode, select the cell where you want to paste the formula, and press Ctrl+V. Excel doesn’t change the cell references when you copy your formula to another cell in this manner. One quick way to change a cell reference from relative to absolute is to select the cell reference in the formula bar and then press F4. Pressing F4 cycles a cell reference through the four possible types of references: = Relative columns and rows (for example, C4) = Absolute columns and rows (for example, $C$4)= Relative columns and absolute rows (for example, C$4) = Absolute columns and relative rows (for example, $C4) To create a formula by entering it in a cell 1. Select the cell in which you want to create the formula. 2. Enter an equal sign (=). 3. Enter the remainder of the formula, and then press Enter. To create a formula by using the Insert Function dialog box 1. On the Formulas tab, in the Function Library group, select the Insert Function button. . Select the function you want to use in your formula. Or N Search for the function you want, and then select it. Select OK. 4, Inthe Function Arguments dialog box, enter the function's arguments. 5. Select OK. » To display the current date and time by using a formula 1. Select the cell in which you want to display the current date and time. 2. Enter =NOW() into the cell. 3. Press Enter. To update a NOW() formula = Press F9. To calculate a payment by using a formula1. Create a formula with the syntax =PMT/rate, nper, pv, fv, type), where: rate is the interest rate, to be divided by 12 for a loan with monthly payments, by 4 for quarterly payments, and so on. nper is the total number of payments for the loan. pvis the amount loaned. fv is the amount to be left over at the end of the payment cycle. type is 0 or 1, indicating whether payments are made at the beginning or at the end of the month. 2. Press Enter. To refer to a named range in a formula 1. Select the cell where you want to create the formula. 2. Enter = to start the formula. 3. Enter the name of the named range in the part of the formula where you want to use its values. 4. Complete the formula. 5. Press Enter. To refer to an Excel table column in a formula 1. Select the cell where you want to create the formula. 2. Enter = to start the formula. 3. At the point in the formula where you want to include the table's values, enter the name of the table. Or Use Formula AutoComplete to enter the table name. 4. Enter an opening bracket ([) followed by the column name OrEnter [ and use Formula AutoComplete to enter the column name. 5. Enter }) to close the table reference. 6. Press Enter. To copy a formula without changing its cell references 1. Select the cell that contains the formula you want to copy. 2. Select the formula text in the formula bar. . Press Ctrl+C. |. Select the cell where you want to paste the formula. - Press Ctrl+V. . Press Enter. PaARw Operators and precedence When you create an Excel formula, you use the built-in functions and arithmetic operators that define operations such as addition and multiplication. In Excel, mathematical operators are evaluated in the order listed in the following table. Operator Description - Negation % Percentage a“ Exponentiation *and/ Multiplication and division +and— Addition and subtraction & Concatenation (adding two strings together)If two operators at the same level, such as + and -, occur in the same equation, Excel evaluates them in left-to-right order. For example, the operations in the formula = 4 + 8 * 3- 6 would be evaluated in this order: 1. 8* 3, with a result of 24 2. 4+ 24, with a result of 28 3. 28 —6, with a final result of 22 You can control the order in which Excel evaluates operations by using parentheses. Excel always evaluates operations in parentheses first. For example, if the previous equation were rewritten as = (4 + 8) * 3— 6, the operations would be evaluated in this order: 4. (4+ 8), with a result of 12 2. 12* 3, with a result of 36 3. 36 —6, with a final result of 30 If you have multiple levels of parentheses, Excel evaluates the expressions within the innermost set of parentheses first and works its way out. As with operations on the same level, such as + and -, expressions in the same parenthetical level are evaluated in left-to-right order. For example, the formula = 4 + (3 + 8 * (2 + 5)) — 7 would be evaluated in this order: 4. (2+ 5), with a result of 7 . 7 * 8, with a result of 56 . 56 + 3, with a result of 59 |. 4 + 59, with a result of 63 . 63 — 7, with a final result of 56 APon To move a formula without changing its cell references1. Select the cell that contains the formula you want to copy. 2. Point to the edge of the cell you selected. 3. Drag the outline to the cell where you want to move the formula. To copy a formula while changing its cell references 1. Select the cell that contains the formula you want to copy. 2. Press Ctrl+C. 3. Select the cell where you want to paste the formula. 4. Press Ctrl+V. To create relative and absolute cell references 1. Enter a cell reference into a formula. 2. Click within the cell reference. 3. Enter a $ in front of a row or column reference you want to make absolute. Or Press F4 to advance through the four possible combinations of relative and absolute row and column references. Summarize data that meets specific conditions Another use for formulas is to display messages when certain conditions are met. This kind of formula is called a conditional formula. One way to create a conditional formula in Excel is to use the /F function. Selecting the Insert Function button next to the formula bar and then choosing the /F function displays the Function Arguments dialog box with the fields required to create an /F formula.Funetion Arguments 27 x ® Logical test | Value tre Value false 4] - ary Checks whether a condition met and returns one vale if TRUE, and ancther value iF FALSE Logical testis any valu or enpression that can be evaluated to TRUE or FALSE The Function Arguments dialog box for an IF formula When you work with an JF function, the Function Arguments dialog box has three boxes: = Logical_test This box holds the condition you want to check. = Value_if_true This box holds the message that will be displayed if the condition is met, enclosed in quotes. For example, in this case, you could type “High-volume shipper—evaluate for rate decrease.” = Value_if_false This box holds the message that will be displayed if the condition is not met, enclosed in quotes. For example, in this case, you could type “Does not qualify at this time.” Excel also includes several other conditional functions you can use to summarize your data, as shown in the following table. Function Description AVERAGEIF _ Finds the average of values within a cell range that meet a specified criterion AVERAGEIFS Finds the average of values within a cell range that meet multiple criteriaFunction Description COUNT Counts the number of cells in a range that contain a numerical value COUNTA Counts the number of cells in a range that are not empty COUNTBLANK Counts the number of cells in a range that are empty COUNTIF Counts the number of cells in a range that meet a specified criterion COUNTIFS Counts the number of cells in a range that meet multiple criteria IFERROR Displays one value if a formula results in an error and another if it doesn't SUMIF Finds the sum of values in a range that meet a single criterion SUMIFS Finds the sum of values in a range that meet multiple criteria As noted, the COUNTIF function counts the number of cells that meet a criterion, the SUMIF function finds the total of values in cells that meet a criterion, and the AVERAGEIF function finds the average of values in cells that meet a criterion. To create a formula that uses the AVERAGEIF function, you define the range to be examined for the criterion, the criterion, and, if required, the range from which to draw the values. As an example, consider a worksheet that lists each customer’s ID number, name, state, and total monthly shipping bill. If you want to find the average order of customers from the state of Washington (abbreviated in the worksheet as WA), you can create the formula =AVERAGEIF(C3:C6, “WA”, D3:D6).A B c D 1 CustomerID CustomerName State Total 2 |CN100 Contoso WA $ 118,476.00 3 CN101 Fabrikam WA $ 125,511.00 4 cN102 Northwind Trade OR 5 |cn103 Adventure Work: WA A list of data that contains customer information The AVERAGEIFS, SUMIFS, and COUNTIFS functions extend the capabilities of the AVERAGEIF, SUMIF, and COUNTIF functions to allow for multiple criteria. For example, if you want to find the sum of all orders of at least $100,000 placed by companies in Washington, you can create the formula =SUMIFS(D3:D6, C3:C6, “=WA”, D3:D6, “>=100000"). The AVERAGEIFS and SUMIFS functions start with a data range that contains values that the formula summarizes. You then list the data ranges and the criteria to apply to that range. In generic terms, the syntax is =AVERAGEIFS(data_range, criteria_range1, criteria1[, criteria_range2, criteria2...]). The part of the syntax in brackets (which aren't used when you create the formula) is optional, so an AVERAGEIFS or SUMIFS formula that contains a single criterion will work. The COUNTIFS function, which doesn't perform any calculations, doesn't need a data range; you just provide the criteria ranges and criteria. For example, you could find the number of customers from Washington who were billed at least $100,000 by using the formula =COUNTIFS(D3:D6, “=WA’, E3:E6, “>=100000"). You can use the /FERROR function to display a custom error message instead of relying on the default Excel error messages to explain what happened. For example, you could create this type of formula to employ the VLOOKUP function to look up a customer's name in the second column of a table named Customers based on the customer identification number entered into cell G8. That formula might look like this: =/FERROR(VLOOKUP(G8, Customers, 2, FALSE), "Customer notfound"). If the function finds a match for the CustomerID in cell G8, it displays the customer's name; if not, it displays the text Customer not found. Gre The last two arguments in the VLOOKUP function tell the formula to look in the Customers table’s second column and to require an exact match. For more information about the VLOOKUP function, see “Look up information in a worksheet” in Chapter 8, “Reorder and summarize data.” To summarize data by using the /F function 1. Select the cell in which you want to enter the formula. 2. Enter a formula with the syntax =/F(Logical_test, Value_if_true, Value_if_false) where: Logical_test is the logical test to be performed. ° Value_if_true is the value the formula returns if the test is true. © Value_if_false is the value the formula returns if the test is false. To create a formula by using the Insert Function dialog box 1. To the left of the formula bar, select the Insert Function button. 2. In the Insert Function dialog box, select the function you want to use in your formula. 3. Select OK. 4. Inthe Function Arguments dialog box, define the arguments for the function you chose. 5. Select OK.To count cells that contain numbers in a range 1. Select the cell in which you want to enter the formula. 2. Create a formula with the syntax =COUNT(range), where range is the cell range in which you want to count cells. To count cells that are non-blank 1. Select the cell in which you want to enter the formula. 2. Create a formula with the syntax =COUNTA(range), where range is the cell range in which you want to count cells. To count cells that contain a blank value 1. Select the cell in which you want to enter the formula. 2. Create a formula with the syntax =COUNTBLANK(range), where range is the cell range in which you want to count cells. To count cells that meet one condition 1. Select the cell in which you want to enter the formula. 2. Enter a formula of the form =COUNTIF(range, criteria) where: © range is the cell range that might contain the criteria value. ° criteria is the logical test used to determine whether to count the cell. To count cells that meet multiple conditions 1. Select the cell in which you want to enter the formula. 2. Enter a formula of the form =COUNTIFS(criteria_range, criteria,...) where for each criteria_range and criteria pair: © criteria_range is the cell range that might contain the criteria value © criteria is the logical test used to determine whether to count the cell.To find the sum of data that meets one condition 1. Select the cell in which you want to enter the formula. 2. Enter a formula of the form =SUMIF(range, criteria, sum_range) where: range is the cell range that might contain the criteria value. criteria is the logical test used to determine whether to include the cell. sum_range is the range that contains the values to be included if the range cell in the same row meets the criterion. To find the sum of data that meets multiple conditions 1. Select the cell in which you want to enter the formula. 2. Enter a formula of the form =SUMIFS(sum_range, criteria_range, criteria,...) where: © sum_range is the range that contains the values to be included if all criteria_range cells in the same row meet all criteria. criteria_range is the cell range that might contain the criteria value criteria is the logical test used to determine whether to include the cell. To find the average of data that meets one condition 1. Select the cell in which you want to enter the formula. 2. Enter a formula of the form =AVERAGEIF(range, criteria, average_range) where: © range is the cell range that might contain the criteria value.© criteria is the logical test used to determine whether to include the cell. © average_range is the range that contains the values to be included if the range cell in the same row meets the criterion. To find the average of data that meets multiple conditions 1. Select the cell in which you want to enter the formula. 2. Enter a formula of the form =AVERAGEIFS(average_range, criteria_range, criteria,...) where: © average_range is the range that contains the values to be included if all criteria_range cells in the same row meet all criteria. criteria_range is the cell range that might contain the criteria value criteria is the logical test used to determine whether to include the cell. To display a custom message if a cell contains an error 1. Select the cell in which you want to enter the formula. 2. Enter a formula with the syntax =/FERROR(value, value_if_error) where: © value is a cell reference or formula. o value_if_error is the value to be displayed if the value argument returns an error. Set iterative calculation options and enable or disable automatic calculation Excel formulas use values in other cells to calculate their results. If you create a formula that refers to the cell that contains the formula, the result is a circular reference.Under most circumstances, Excel treats a circular reference as a mistake for two reasons. First, the vast majority of Excel formulas don't refer to their own cell, so a circular reference is unusual enough to be identified as an error. The second, more serious consideration is that a formula with a circular reference can slow down your workbook. Because Excel repeats, or iterates, the calculation, you need to set limits on how many times the app repeats the operation. You can contro! your workbook's calculation options by using the controls on the Formulas page of the Excel Options dialog box. vs ange options flsted to Sirus calclation, performance, and ero ning, Formals awed Set iterative calculation options on the Formulas page of the Excel Options dialog box The Calculation Options section of the Excel Options dialog box has three available settings:= Automatic The default setting; recalculates a worksheet whenever a value affecting a formula changes = Automatic except for data tables Recalculates a worksheet whenever a value changes, but doesn’t recalculate data tables = Manual Requires you to press F9, or, on the Formulas tab, in the Calculation group, select the Calculate Now button to recalculate your worksheet You can also use options in the Calculation Options section to allow or disallow iterative calculations. If you select the Enable Iterative Calculation check box, Excel repeats calculations for cells that contain formulas with circular references. The default Maximum Iterations value of 100 and Maximum Change of 0. 001 are appropriate for all but the most unusual circumstances. Gre You can also control when Excel recalculates its formulas by selecting the Formulas tab on the ribbon, selecting the Calculation Options button, and selecting the behavior you want. To recalculate a workbook 1. Display the workbook you want to recalculate. 2. Press F9. Or On the Formulas tab, in the Calculation group, select Calculate Now. To recalculate a worksheet 1. Display the worksheet you want to recalculate. 2. Inthe Calculation group, select the Calculate Sheet button.To set worksheet calculation options 1. Display the worksheet whose calculation options you want to set. 2. In the Calculation group, select the Calculate Options button. 3. Select the calculation option you want in the list. To set iterative calculation options 1. Display the Backstage view, and then select Options. 2. Inthe Excel Options dialog box, click Formulas. 3. In the Calculation options section, select or clear the Enable iterative calculation check box. 4. In the Maximum Iterations box, enter the maximum iterations allowed for a calculation. 5. In the Maximum Change box, enter the maximum change allowed for each iteration. 6. Select OK. Use array formulas Most Excel formulas calculate values to be displayed in a single cell. For example, you could add the formulas =B1*B4, =B1*B5, and =B1*B6 to consecutive worksheet cells to calculate shipping insurance costs based on the value of a package’s contents. A a (emmy lf 1 Insurance Rate 2.25% 2 3 PackagelD Value __ Premium 4 |pk101352 $ 591.00 ] 5 |PK101353 $1,713.00 6 Pk101354 $3,039.00 7 A worksheet with data to be summarized by an array formulaRather than add the same formula to multiple cells one cell at a time, you can add a formula to every cell in the target range at the same time by creating an array formula. To create an array formula, you enter the formula’s arguments and press Ctrl+Shift+Enter to identify the formula as an array formula. To calculate package insurance rates for values in the cell range B4:B6 and the rate in cell B1, you would select a range of cells with the same shape as the value range and enter the formula =B1*B4:B6. In this case, the values are in a three- cell column, so you must select a range of the same shape, such as C4:C6. When you press Ctrl+Shift+Enter, Excel creates an array formula in the selected cells. The formula appears within a pair of braces to indicate that it is an array formula. ca = fe | {=B1*B4:B6} A 8 c D 1 Insurance Rate 2.25% 2 3 PackagelD Value Premium 4 |Pk101352 $ 591.00 13.2975) 5 |PK101353 $1,713.00 38.5425 6 |PK101354 $3,039.00 68.3775, if a 8 ‘A worksheet with an array formula ready to be entered A IMPORTANT If you enter the array formula into a range of the wrong shape, Excel displays duplicate results, incomplete results, or error messages, depending on how the target range differs from the value rangeA IMPORTANT You can't add braces to a formula to make it an array formula. You must press Ctrl+Shift+Enter to create it. In addition to creating an array formula that combines a single cell's value with an array, you can create array formulas that use two separate arrays. For example, a company might establish a goal to reduce sorting time in each of four distribution centers. This worksheet stores the previous sorting times in minutes in cells B2:B5, and the percentage targets in cells C2:C5. The array formula to calculate the targets for each of the four centers is =B2:B5*C2:C5, which, when you enter it into cells D2:D5 by pressing Ctrl+Shift+Enter, would appear as {= B2:B5*C2:C5}. A 8 ie D 1 [Center Previous Time Target Percentage Target Time 2 [North 145 85% ] 3. South 180 90% 4 East 195 75% 5 West 205 70% 6 ‘A worksheet with data for an array formula that mutiplies two arrays To edit an array formula, you must select every cell that contains the array formula, click the formula bar to activate it, edit the formula in the formula bar, and then press Ctrl+Shift+Enter to re-enter the formula as an array formula. To create or edit an array formula Gre Many operations that used to require an array formula can now be calculated by using functions such as SUMIFS and COUNTIFS. 1. Select the cells to include in the array formula2. Enter or edit your array formula. 3. Press Ctrl+Shift+Enter. Find and correct errors in calculations Including calculations in a worksheet gives you valuable answers to questions about your data. As is always true, however, it is possible for errors to creep into your formulas. With Excel, you can find the source of errors in your formulas by identifying the cells used in a specific calculation and describing any errors that have occurred. The process of examining a worksheet for errors is referred to as auditing. Excel identifies errors in several ways. The first way is to display an error code in the cell holding the formula generating the error. 10 Loading Dock ai) Concrete $ 2,169.00 12 labor $ 4,500.00 13 Posts $ 300,00 14 Excavation $ 2,500.00 15. Drain $ 1,800.00 16 Rails $ 495.00 7 Stairs S$ 1,295.00 18 Subtotal $ 13,059.00 19 20 Build Total $31,385.00 21 Labor Percentage » 22 23 A worksheet with an error code displayed When a cell with an erroneous formula is the active cell, Excel displays an Error button next to it. If you point to the Error button, Excel displays an arrow on the button’s right edge. Clicking the arrow displays a menu with options that provide information about the error and offer to help you fix it.The following table lists the most common error codes and what they mean. Error Description code #HH#HH# = The column isn't wide enough to display the value. #VALUE! The formula has the wrong type of argument, such as text in a cell where a numerical value is required #NAME? The formula contains text that Excel doesn't recognize, such as an unknown named range. #REF! The formula refers to a cell that doesn't exist, which can happen whenever cells are deleted. #DIV/O!_ The formula attempts to divide by zero. Another technique you can use to find the source of formula errors is to ensure that the appropriate cells are providing values for the formula. You can identify the source of an error by having Excel trace a cell's precedents, which are the cells with values used in the active cell's formula. You can also audit your worksheet by identifying cells with formulas that use a value from a particular cell. Cells that use another cell's value in their calculations are known as dependents, meaning that they depend on the value in the other cell to derive their own value. They are identified in Excel by tracer arrows. If the cells identified by the tracer arrows aren't the correct cells, you can hide the arrows and correct the formula.10 Loading Dock 1" Concrete 12 labor 13 Posts 14 Excavation 15 Drain 16 Rails 7 Stairs 18 Subtotal 19 20 Build Total 21 Labor Percentage A worksheet showing a cell's dependents $* 13,059.00 31,385.00 34% If you prefer to have the elements of a formula error presented as text in a dialog box, you can use the Error Checking dialog box to locate errors one after the other, choose to ignore the selected error, or move to the next or the previous error. Error Checking Error in coll D21 =c12/019 Divide by Zero Error The formula or function used is dividing by zero or empty cells. Options. Help on this Error Shovr Calculation Steps.. Ignore Error Edit in Formula Bar Previous Newt Identify and manage errors using the Error Checking dialog boxS Tip You can have the Error Checking tool ignore formulas that don't use every cell in a region (such as a row or column). To do so, on the Formulas tab of the Excel Options dialog box, clear the Formulas Which Omit Cells In A Region check box. Excel will no longer mark these cells as an error. When you just want to display the results of each step of a formula and don't need the full power of the Error Checking tool, you can use the Evaluate Formula dialog box to move through each element of the formula. The Evaluate Formula dialog box is particularly useful for examining formulas that don’t produce an error but aren't generating the result you expect. Evaluate Formula 2 x Reference: Eyaluation FirstBidi$D521 ~ ferz/20 ‘To show the result of the underlined expression click Evaluate, The mos italicized Step in Step Out ‘Glese Step through formulas by using the Evaluate Formula dialog box Find and correct errors in calculations Finally, you can monitor the value in a cell regardless of where you are in your workbook by opening a Watch Window that displays the value in the cell. For example, if one of your formulas uses values from cells in other worksheets or even other workbooks, you can set a watch on the cell that contains the formula, and then change the values in the othercells. As soon as you enter the new value, the Watch Window displays the new result of the formula. When you're done watching the formula, you can delete the watch and hide the Watch Window. vx Watch Window ?3 Add Watch... Book Sheet Name Cell Value Formula Aucitfor. FistBid Die ——_$1305900 =SuM(C11C17) Follow celll values by using the Watch Window To display information about a formula error 1. Select the cell that contains the error. 2. Point to the error indicator next to the cell. Or Select the error indicator to display more information. To display tracer arrows identifying formula precedents = On the Formulas tab, in the Formula Auditing group, select the Trace Precedents button. To display tracer arrows identifying cell dependents = Inthe Formula Auditing group, select the Trace Dependents button. To remove tracer arrows = Do either of the following:o Inthe Formula Auditing group, select the Remove Arrows button (not its arrow). © Select the Remove Arrows arrow and select the arrows you want to remove. To evaluate a formula one calculation at a time 1. Select the cell that contains the formula you want to evaluate. 2. Inthe Formula Auditing group, select the Evaluate Formula button. In the Evaluate Formula dialog box, select Evaluate. . Select Step In to move forward by one calculation. Or Select Step Out to move backward by one calculation. ay 5. Select Close. To change error display options 1. Display the Backstage view, and then click Options. 2. Inthe Excel Options dialog box, select Formulas. 3. In the Error Checking section, select or clear the Enable background error checking check box. 4. Select the Indicate errors using this color button and select a color. 5. Select Reset Ignored Errors to return Excel to its default error indicators. 6. Inthe Error checking rules section, select or clear the check boxes next to errors you want to indicate or ignore, respectively. To watch the values in a cell range 1. Select the cell range you want to watch. 2. Inthe Formula Auditing group, select the Watch Window button.3. In the Watch Window dialog box, select Add Watch. 4. Select Add. To delete a watch 1. Select the Watch Window button. 2. In the Watch Window dialog box, select the watch you want to delete. 3. Select Delete Watch Key points In this chapter, you learned how to: Name groups of data Create formulas to calculate values Summarize data that meets specific conditions Set iterative calculation options and enable or disable automatic calculation Use array formulas Find and correct errors in calculations Key points 7) Practice tasks Before you can complete these tasks, you need to copy the book’s practice files to your computer. The practice files for these tasks are located in the Office2019SBS\Ch06 folder. You can save the results of the tasks in the same folder. The introduction includes a complete list of practice files and download instructions. Name groups of dataOpen the CreateNames workbook in Excel, and then complete the following tasks: 1. Create a named range named Monday for the V_101 through V_109 values (found in cells C4:C12) for that weekday. 2. Edit the Monday named range to include the V_110 value for that column. 3. Select cells B4:H13 and create named ranges for V_101 through V_110, drawing the names from the row headings. 4. Delete the Monday named range. Create formulas to calculate values Open the BuildFormulas workbook in Excel, and then complete the following tasks: 1. On the Summary worksheet, in cell F9, create a formula that displays the value from cell C4. 2. Edit the formula in cell F9 so it uses the SUM function to find the total of values in cells C3:C8. 3. In cell F10, create a formula that finds the total expenses for desktop software and server software. 4. Edit the formula in F10 so the cell references are absolute references, 5. On the JuneLabor worksheet, in cell F13, create a SUM formula that finds the total of values in the JuneSummary table's Labor Expense column. Summarize data that meets specific conditions Open the CreateConditionalFormulas workbook in Excel, and then complete the following tasks: 1. In cell G3, create an /F formula that tests whether the value in F3 is greater than or equal to 35,000. If it is, display Requestdiscount; if not, display No discount available. - Copy the formula from cell G3 to the range G4:G14. . In cell 13, create a formula that finds the average cost of all expenses in cells F3:F14 where the Type column contains the value Box. . In cell 16, create a formula that finds the sum of all expenses in cells F3:F14 where the Type column contains the value Envelope and the Destination column contains the value International. Set iterative calculation options and enable or disable automatic calculation Open the SetlterativeOptions workbook in Excel, and then complete the following tasks: 1. x - pe 2 n On the Formulas tab, in the Calculation group, select the Calculation Options button, and then select Manual. In cell B6, enter the formula =B7*B9, and then press Enter. Note that this result is incorrect because the Gross Savings value minus the Savings Incentive value should equal the Net Savings value, which it does not. Press F9 to recalculate the workbook and read the message box indicating that you have created a circular reference. Select OK. Use options in the Excel Options dialog box to enable iterative calculation. . Close the Excel Options dialog box and recalculate the worksheet. Change the workbook’s calculation options to Automatic. Use array formulasOpen the CreateArrayFormulas workbook in Excel, and then complete the following tasks: 1. On the Fuel worksheet, select cells C11:F11. 2. Enter the array formula =C3*C9:F9 in the selected cells. Paw . Edit the array formula you just created to read =C3*C10:F10. . Display the Volume worksheet. . Select cells D4:D7. . Create the array formula =B4:B7*C4:C7. Find and correct errors in calculations Open the AuditFormulas workbook in Excel, and then complete the following tasks: 1. a Create a watch that displays the value in cell D20. 2. Select cell D8, and then display the formula’s precedents. 3. 4. Select cell A1, and then use the Error Checking dialog box to Remove the tracer arrows from the worksheet. identify the error in cell D21. . Show the tracer arrows for the error. . Remove the arrows, and then edit the formula in cell D21 so it reads =C12/D20. . Use the Evaluate Formula dialog box to evaluate the formula in cell D21. . Delete the watch you created in step 1