Performing Calculations on Data

Advanced Training for Microsoft Office Excel 2003

Performing Calculations on Data

Naming Cell Ranges

1	To begin creating a named range, click cell C3 and drag to cell C13.
2	To create a named range by using the Create command, click the Insert menu, point to Name, and then click Create.
3	To accept the top row label of Price as the name of the cell range, in the Create Names dialog box, verify that the Top row check box is selected by default, and click OK.
4	To view the Supplies worksheet, click the Supplies sheet tab.
5	To select the cells that need to be included in the cell range, click cell C4 and drag to cell C13.
6	To define a named range by using the Define command, click the Insert menu, point to Name, and then click Define.
7	To assign a name to the cell range, for the purposes of this exercise, press SPACEBAR to have the name typed for you in the Define Name dialog box, and click OK.
8	To view the Furniture worksheet, click the Furniture sheet tab.
9	To select the cells that need to be included in the cell range, click cell C4 and drag to cell C13.
10	To assign a name to the cell range by using the Name box, click in the Name box, as indicated, press SPACEBAR to have the name typed for you, and then press ENTER.
11	To begin using a named range reference in a formula, click cell C15.
12	To begin using a function, click the Insert menu, and then click Function.
13	To calculate the total cost of the furniture, in the Select a function list, verify that SUM is selected by default, and then click OK.
14	To use a named range reference in a formula, press SPACEBAR to have the name typed for you, and then click OK.
15	Notice that the sum $642.60 is now displayed in cell C15. To begin editing a named range, click the Insert menu, point to Name, and then click Define.
16	To select an existing name, in the Define Name dialog box, in the Names in workbook list, click Price.
17	To edit the name, double-click in the Names in workbook box, press SPACEBAR to have the new name typed for you, and then click Add.
18	To delete the original name, click Price, click Delete, and then to close the dialog box, click OK.
19	To save the changes, on the Standard toolbar, click the Save button.

Creating Formulas to Calculate Values

1	To begin creating a formula that will return the sum of the contents of other cells, click the Formula Bar, and then, for the purposes of this exercise, press SPACEBAR, and =SUM( will be typed for you.
2	To select the cells that will be used to calculate the sum, drag from cell D4 to cell D5, and then press ENTER.
3	To copy a formula from a cell to the Clipboard, click cell D7, and then on the Standard toolbar, click the Copy button.
4	To paste the copied formula from the Clipboard to another cell, click cell D8, and then on the Standard toolbar, click the Paste button.
5	To delete the formula in cell D8 and begin inserting a new function there, press the DELETE key, click the Insert menu, and click Function.
6	To insert a function that calculates the average of a group of values, in the Insert Function dialog box, in the Select a function box, click AVERAGE, and then click OK.
7	To specify which cells should be used to calculate the average, in the Function Arguments dialog box, press SPACEBAR, and the name of a cell range will be typed for you in the Number1 box.
8	To finish inserting the completed formula in cell D8, in the Function Arguments dialog box, click OK.
9	Notice that the formula appears in the Formula Bar, and the calculated result appears in cell D8. To insert another function in a cell, click cell C10, click the Insert menu, and click Function.
10	To begin creating a conditional formula that inserts some text if a cell contains a number larger than 50, in the Insert Function dialog box, in the Select a function box, click IF, and then click OK.
11	To specify the condition for the formula, in the Function Arguments dialog box, press SPACEBAR, and D7>50 will be typed for you in the Logical_test box.
12	To display the words 5% discount if the value in D7 is greater than 50, press TAB to move the cursor to the Value_if_true box, and then press SPACEBAR, and the text will be typed for you.
13	To complete the conditional formula, press TAB to move to the Value_if_false box, and then press SPACEBAR, and No discount will be typed for you.
14	To apply the conditional formula and see whether this order is large enough to qualify for a discount, in the Function Arguments dialog box, click OK.

Finding and Correcting Errors in Calculations

1	To begin finding and correcting errors in calculations, click cell D20.
2	To view the type of error in cell D20, click the Error Options button to the left of cell D20, notice that Divide by Zero Error displays at the top of the menu, and then click the Error Options button again.
3	To constantly monitor the value in a cell, click the Tools menu, point to Formula Auditing, and then click Show Watch Window.
4	To add a watch for the currently selected cell (D20), in the Watch Window, click Add Watch, and then, in the Add Watch dialog box, click Add.
5	To trace the precedents of a cell value, click cell D8, click the Tools menu, point to Formula Auditing, and then click Trace Precedents.
6	To remove the arrows tracing the precedents of a cell, click the Tools menu, point to Formula Auditing, and then click Remove All Arrows.
7	To check for errors in the worksheet, click the Tools menu, and click Error Checking.
8	Notice that an error in cell D20 is described in the Error Checking dialog box. To see if there are additional errors, click Next.
9	To close the dialog boxes and begin tracing the error in cell D20, in the message box telling you that the error check is complete, click OK.
10	To trace the error in cell D20, click the Tools menu, point to Formula Auditing, and then click Trace Error.
11	The blue tracer arrows indicate the cell values generating the error in cell D20. To remove the error-tracing arrows, click the Tools menu, point to Formula Auditing, and then click Remove All Arrows.
12	To modify the formula in cell D20, click to the right of the text in the Formula Bar, point to the bottom border of the blue box surrounding cell D7, and drag this box to cell D8.
13	To change the second cell used in the formula, point to bottom border of the green box surrounding cell D15, and drag it to cell D16.
14	To view the results of the modified formula in cell D20, press ENTER.
15	To begin evaluating the modified formula, click cell D20, click the Tools menu, point to Formula Auditing, and then click Evaluate Formula.
16	To view the result of the evaluated formula, in the Evaluate Formula dialog box, click Evaluate.
17	To return to the worksheet, in the Evaluate Formula dialog box, click Close.
18	To remove the watch for cell D20, in the Watch Window, click the row that starts with Orders.xls, and then click Delete Watch.
19	To close the Watch Window, click the Tools menu, point to Formula Auditing, and then click Hide Watch Window.