7 Performing Calculations on Data

To create a named range from selected cells

  1. On the Formula Bar, click the Name Box.

  2. Type the name you want for the range, and then press .

To create a named range from a selection including the name

  1. Select the cells you want to name as a range. Be sure either the first or last cell contains the name for the range.

  2. On the Formulas tab, in the Defined Names group, click Create from Selection.

  3. Select the check box for the cell that contains the name you want. Then click OK.

To display the Name Manager

  • On the Formulas tab, in the Defined Names group, click Name Manager.

To edit a named range

  1. On the Formulas tab, in the Defined Names group, click Name Manager.

  2. Click the named range you want to edit, and then click the Edit button.

  3. Click the Collapse Dialog button, select the cells you want in the range, and then click Close.

To create a formula

  1. Click the cell into which you want to enter a formula.

  2. Type =.

  3. Type the expression representing the calculation you want to perform.

  4. Press .

To create a formula by using the Insert Function dialog box

  1. On the Formulas tab, in the Function Library group, click Insert Function.

  2. Select the function you want to use, and then click OK.

  3. Fill in the Function Arguments dialog box, and then click OK.

To refer to a table column or row in a formula

  1. Click the cell in which you want to create the formula.

  2. Type =, followed by the function to include in the formula and a left parenthesis, for example, =SUM(.

  3. Move the mouse pointer over the header of the table column you want to use in the formula. When the mouse pointer changes to a black, downward-pointing arrow, click the column header.

  4. Type a right parenthesis, and then press .

To create a formula that doesn't change when copied between cells

  • Precede all column and row references with a dollar sign, for example, $C$4.

To create a conditional formula

  1. Click the cell in which you want to enter an IF function.

  2. On the Formulas tab, in the Function Library group, click Logical, and then click IF.

  3. Type a conditional statement that evaluates to true or false. Type the text you want to appear if the condition is true, and the text you want to appear if the condition is false. Then click OK.

To display cells that provide values for a formula

  1. Click the cell you want to track.

  2. On the Formulas tab, in the Formula Auditing group, click the Trace Precedents button.

To display formulas that use a cell's contents

  1. Click the cell you want to track.

  2. On the Formulas tab, in the Formula Auditing group, click the Trace Dependents button.

To remove tracer arrows

  1. Click the cell you want to track.

  2. On the Formulas tab, in the Formula Auditing group, click the Remove Arrows button.

To locate errors in a worksheet

  1. On the Formulas tab, in the Formula Auditing group, click the Error Checking button.

  2. Click the Edit in Formula Bar button.

  3. Edit the formula.

  4. Click the Next button to view the next error.

To step through a formula to locate an error

  1. Click the cell with the formula you want to evaluate.

  2. On the Formulas tab, in the Formula Auditing group, click Evaluate Formula.

  3. Click Evaluate (one or more times) to move through the formula's elements.

To watch a value in a cell

  1. On the Formulas tab, in the Formula Auditing group, click Watch Window.

  2. Click Add Watch, select the cells you want to watch, and then click Add.

  3. Click Watch Window.

To delete a watch

  1. On the Formulas tab, in the Formula Auditing group, click Watch Window.

  2. Click the watch you want to delete, and then click Delete Watch.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset