# Perform calculations on data

- By Curtis Frye
- 4/9/2019

## Practice tasks

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

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 named ranges for V_101 through V_110, drawing the names from the row headings.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*.

### Set iterative calculation options and enable or disable automatic calculation

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

On the

**Formulas**tab, in the**Calculation**group, click the**Calculation Options**button, and then click**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.Click

**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 perform the following tasks:**

On the

**Fuel**worksheet, select cells**C11:F11**.Enter the array formula

**=C3*C9:F9**in the selected cells.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 perform the following tasks:**

Create a watch that displays the value in cell

**D20**.Click cell

**D8**, and then display the formula’s precedents.Remove the tracer arrows from the worksheet.

Click cell

**A1**, and then use the**Error Checking**dialog box to 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.