Perform calculations on data

Practice tasks

Before you can complete these tasks, you must copy the book’s practice files to your computer. The practice files for these tasks are in the Excel365SBS\Ch03 folder. You can save the results of the tasks in the same folder.

Name data ranges

Open the NameRanges 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 a batch of named ranges for V_101 through V_110, using the row headings as the range names.

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.

Create array formulas

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

1. On the Fuel worksheet, in cells C11:F11, enter the array formula =C3*C9:F9.

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

3. On the Volume worksheet, in 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 perform the following tasks:

1. Set a watch on the value in cell C19.

2. Display the precedents for the formula in cell C7.

3. Hide the tracer arrows.

4. Use the Error Checking dialog to identify the error in cell C20.

5. Show the tracer arrows for the error.

6. Hide the arrows, and then change the formula in cell C20 to =C12/D20.

7. Use the Evaluate Formula dialog to step through the formula in cell C20.

8. Delete the watch you created in step 1.

Configure automatic and iterative calculation options

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

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

2. In cell B6, enter the formula =B7*B9, and then press Enter.

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

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

5. Select OK.

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

7. Close the Excel Options dialog and recalculate the worksheet.

8. Change the workbook’s calculation options back to Automatic.