# Perform calculations on data

- By Curtis Frye and Joan Lambert
- 1/29/2022

## 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:

Create a named range named

**Monday**for the V_101 through V_109 values (found in cells**C4:C12**) for that weekday.Edit the

**Monday**named range to include the V_110 value for that column.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.Delete the

**Monday**named range.

### Create formulas to calculate values

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

On the

**Summary**worksheet, in cell**F9**, create a formula that displays the value from cell**C4**.Edit the formula in cell

**F9**so it uses the SUM function to find the total of values in cells**C3:C8**.In cell

**F10**, create a formula that finds the total expenses for desktop software and server software.Edit the formula in

**F10**so the cell references are absolute references.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:

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**.Copy the formula from cell

**G3**to the range**G4:G14**.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*.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:

On the

**Fuel**worksheet, in cells**C11:F11**, enter the array formula**=C3*C9:F9**.Edit the array formula you just created to read

**=C3*C10:F10**.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:

Set a watch on the value in cell

**C19**.Display the precedents for the formula in cell

**C7**.Hide the tracer arrows.

Use the

**Error Checking**dialog to identify the error in cell**C20**.Show the tracer arrows for the error.

Hide the arrows, and then change the formula in cell

**C20**to**=C12/D20**.Use the

**Evaluate Formula**dialog to step through the formula in cell**C20**.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:

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 to enable iterative calculation.Close the

**Excel Options**dialog and recalculate the worksheet.Change the workbook’s calculation options back to

**Automatic**.