Core Training for Microsoft Office XP

Performing Calculations on Data

Naming Groups of Data

1 To select the range of cells you are going to name, point to cell C3, hold down the left mouse button, drag the pointer to cell C18, and release the mouse button.
2 To give the selected range a name, click the Insert menu, point to Name, and then click Create.
3 To use the name from the top cell in the selected range (Price) as the name for this range, in the Create Names dialog box, click OK.
4 To move to a new worksheet, in the lower-left corner of the workbook window, click the Supplies sheet tab.
5 To give the cell range C4 through C29 the name SuppliesPrice, click cell C4, click once below the scroll box in the vertical scroll bar, hhold down the SHIFT key, click cell C29, and release the SHIFT key.
6 Click the Insert menu, point to Name, and then click Define.
7 In the Names in workbook box, type SuppliesPrice and then click OK.
8 To move to the Furniture worksheet, click the Furniture sheet tab.
9 To give the cell range C4 through C18 the name FurniturePrice, point to cell C4, hold down the left mouse button, drag to cell C18, and release the mouse button.
10 Just above the upper-left corner of the worksheet, click in the Name box.
11 Type FurniturePrice, and press the ENTER key on your keyboard.
12 To edit the name of the first range you created so that it matches the other two named ranges, click the Tools sheet tab, and then click the Insert menu, point to Name, and click Define.
13 In the Define Name dialog box, in the list below the Names in workbook box, click Price.
14 In the Names in workbook box, click to the left of Price, type Tools, and then click Add.
15 In the Names in workbook list, click Price, click Delete, and then click OK.

Creating Formulas to Calculate Values

1 To enter a formula in cell D6 that calculates the sum of the values in cells D3 and D4, click cell D6, type =D3+D4, and press the ENTER key on your keyboard.
2 To calculate the average price of items in Order #1, click the Insert menu, and click Function.
3 In the Insert Function dialog box, in the Select a Function list, click AVERAGE and then click OK.
4 The cell range D3:D4 is named OrderItems1, so type OrderItems1 and then click OK.
5 To use the SUM function to calculate a subtotal for Order #2, click cell D16, and, to the left of the Formula bar, click the Insert Function button.
6 In the Insert Function dialog box, in the Select a function pane, click SUM and then click OK.
7 In the Function Arguments dialog box, click OK.
8 To copy the formula from cell D16 to cell D26 and have Excel adjust the cell references in the formula to calculate the subtotal for Order #3, on the Standard toolbar, click the Copy button.
9 In the vertical scroll bar, click the down scroll arrow four times.
10 Click cell D26, and then, on the Standard toolbar, click the Paste button.
11 Notice that Excel has adjusted the formula you see in the Formula bar, automatically replacing the cell range D10:D15 with the cell range D20:D25. To scroll back up the page, click once above the scroll box in the vertical scroll bar.
12 Orders in which the average price is over $50 should receive a 5% discount. To create a conditional formula that tells if an order qualifies for this discount, click cell E7, click the Insert menu, and click Function.
13 In the Insert Function dialog box, in the Select a function list, click IF and then click OK.
14 In the Function Arguments dialog box, in the Logical_test box, type D7>50.
15 Click in the Value_if_true box, and type "5% discount" (be sure to type the quotation marks).
16 Click in the Value_if_false box, type "No discount" (including quotation marks), and click OK.
17 To copy the conditional formula from cell E7 to E14 and E26, on the Standard toolbar, click the Copy button.
18 Click cell E16, and on the Standard toolbar, click the Paste button.
19 Click once beneath the scroll box in the vertical scroll bar, click cell E26, and on the Standard toolbar, click the Paste button.

Finding and Correcting Errors in Calculations

1 To check whether the total for Order#1 has been calculated using the prices from Order #1, click cell D8, click the Tools menu, point to Formula Auditing, and click Trace Precedents.
2 To check whether the total for Order#2 has been calculated using the prices from Order #2, click cell D16, click the Tools menu, point to Formula Auditing, and click Trace Precedents.
3 Cell D16 appears to be using the wrong cells in its calculation. To remove the precedents arrows, click the Tools menu, point to Formula Auditing, and then click Remove All Arrows.
4 To correct the error in D16, in the Formula bar, click the cell range C2:C6 (between the parentheses).
5 On the worksheet, point to the bottom edge of the blue box surrounding cells C2:C6, hold down the left mouse button, drag the box down the page until it encloses cells C10:C14, and release the mouse button.
6 To see the recalculated total in cell D16, press ENTER.
7 To trace the error that Excel has identified for you with an error code, click cell D20.
8 Click the Error button to the left of D20, and in the menu that displays, click Show Formula Auditing Toolbar.
9 On the Formula Auditing toolbar, click the Trace Error button (sixth button from right).
10 On the Formula Auditing toolbar, click the Remove All Arrows button (just left of the Trace Error button).
11 To correct the error, in the Formula bar, click inside the parentheses, just to the right of D15.
12 Press the BACKSPACE key on your keyboard, and type 6.
13 In the Formula bar, double-click the cell reference D7.
14 On the worksheet, click cell D8.
15 To see the recalculated total in cell D20, press ENTER.