Perform calculations on data

The practice files for these tasks are located in the Excel2019SBS\Ch03 folder. You can save the results of the tasks in the same folder.

Name groups of data

Open the CreateNames workbook in Excel, and then perform 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 perform 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 perform the following tasks:

1. In cell G3, create an IF 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.

2. Copy the formula from cell G3 to the range G4:G14.

3. In cell I3, create a formula that finds the average cost of all expenses in cells F3:F14 where the Type column contains the value Box.

4. In cell I6, 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 SetIterativeOptions workbook in Excel, and then perform the following tasks:

1. On the Formulas tab, in the Calculation group, click the Calculation Options button, and then click Manual.

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

3. Press F9 to recalculate the workbook and read the message box indicating that you have created a circular reference.

4. Click OK.

5. Use options in the Excel Options dialog box to enable iterative calculation.

6. Close the Excel Options dialog box and recalculate the worksheet.

7. Change the workbook’s calculation options to Automatic.

Use array formulas

Open the CreateArrayFormulas workbook in Excel, and then perform the following tasks:

1. On the Fuel worksheet, select cells C11:F11.

2. Enter the array formula =C3*C9:F9 in the selected cells.

3. Edit the array formula you just created to read =C3*C10:F10.

4. Display the Volume worksheet.

5. Select cells D4:D7.

6. Create the array formula =B4:B7*C4:C7.

Find and correct errors in calculations

Open the AuditFormulas workbook in Excel, and then perform the following tasks:

1. Create a watch that displays the value in cell D20.

2. Click cell D8, and then display the formula’s precedents.

3. Remove the tracer arrows from the worksheet.

4. Click cell A1, and then use the Error Checking dialog box to identify the error in cell D21.

5. Show the tracer arrows for the error.

6. Remove the arrows, and then edit the formula in cell D21 so it reads =C12/D20.

7. Use the Evaluate Formula dialog box to evaluate the formula in cell D21.

8. Delete the watch you created in step 1.