Performing Calculations on Data

Core Training for Microsoft Office Excel 2003

Performing Calculations on Data

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.

Rounding Results in Statistical Functions

1	To begin inserting a statistical function in a cell, click the Insert menu, and click Function.
2	To select the category of functions, in the Insert Function dialog box, click the arrow next to the Or select a category box, and then click Statistical.
3	Notice the AVEDEV function is selected in the Select a function list. To use this function to calculate the average of absolute deviations of cell data from their mean value, click OK.
4	To begin specifying a range for one of the arguments in this function, in the Function Arguments dialog box, in the Number1 box, click the indicated Collapse button.
5	To define a data range for the argument, drag from cell B2 to cell B8.
6	To accept your selection as the data range for the function, in the Function Arguments dialog box, click the indicated Expand button, and then click OK.
7	The AVEDEV function automatically rounds its result to five decimal places. To round the result to fewer decimal places, in the Formula Bar, click to the right of the equal (=) sign.
8	To nest the AVEDEV function inside another function, for the purposes of this exercise, press SPACEBAR, and text for the ROUND function will be typed for you.
9	To complete the formula so that it rounds the result to two decimal places, in the Formula Bar, click to the right of B8), press SPACEBAR, and ,2) will be typed for you.
10	To display the result of the function in cell B10, which will now be rounded to only two decimal places, press ENTER.