Core Training for Microsoft Excel 2002

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.

Performing Calculations on Filtered Data

1 To create a filter so that of rows 5 to 36, only rows containing the ten highest values in column P will be displayed, click the Data menu, point to Filter, and then click AutoFilter.
2 In cell P5, click the down arrow button and then, from the list that appears, click (Top 10...).
3 To accept the default setting in the Top 10 AutoFilter dialog box (to show the top 10 values in the selected cells), click OK.
4 To view a subtotal for the data now displayed in cells P6 to P27, point to cell P6, hold down the left mouse button, drag to cell P27, and release the mouse button.
5 To insert the subtotal of the selected cells into cell P37, click cell P37, and then, on the Standard toolbar, click the AutoSum button.
6 Press the ENTER key on your keyboard.
7 To edit the formula in cell P38 so that it does not include P37 in its total, in the Formula bar, click between the number 37 and the closing parenthesis, press the BACKSPACE key on your keyboard, type 6, and press ENTER.
8 To change the formula in cell P37 so that it calculates the average of the top 10 values in cells P6 through P36 (instead of the sum), click cell P37.
9 In the Formula bar, click between the number 9 and the comma that follows it.
10 Nine is the function number for calculating a sum in a SUBTOTAL formula. The function number for calculating an average is 1, so press the BACKSPACE key, type 1, and then press the ENTER key.
11 Click cell P38 to select it. Click the Tools menu, then click Goal Seek.
12 The Goal Seek dialog box appears with P38 in the Set cell box. Click the To value box, then type 97800.
13 Click the By changing cell box, type M31, then click OK.
14 The Goal Seek Status box appears giving the target and current values. Click OK.