Microsoft Office Tutorials and References

In Depth Information

**Working with formulas**

as
=Price+(Price*Tax)
, to calculate the cost of items with 8.3 percent sales tax. Note that

named constants and formulas do not appear in the Name box on the formula bar, but

they do appear in the Name Manager dialog box.

You can also enter a formula in the Refers To text box. For example, you might define the

name Price with a formula such as =Sheet1!A1*190%. If you define this named formula

while cell B1 is selected, you can then type
=Price
in cell B1, and the defined formula takes

care of the calculation for you. Because the reference in the named formula is relative, you

can then type
=Price
in any cell in your workbook to calculate a price using the value in

the cell directly to the left. If you type a formula in the Refers To text box that refers to

a cell or range in a worksheet, Excel updates the formula whenever the value in the cell

changes.

Using relative references in named formulas

When you create a named formula that contains relative references, such as

=Sheet1!B22+1.2%, Excel interprets the position of the cells referenced in the Refers To

text box as relative to the cell that is active when you define the name. Later, when you

use such a name in a formula, the named formula uses whatever cell is in the same

relative position. For example, if cell B21 is the active cell when you define the name Fees as

=Sheet1!B22+1.2%, the name Fees always refers to the cell in the same column and one

row below the cell in which the formula is currently located.

Creating three-dimensional names

You can create three-dimensional names, which use 3-D references as their definitions. For

example, suppose you have a 13-worksheet workbook containing one identical worksheet

for each month plus one summary sheet. You can define a 3-D name you can use to

summarize totals from each monthly worksheet. To do so, follow these steps:

1.
Select cell B5 in Sheet1 (the summary sheet).

2.
Click the Define Name button.

Type
Three_D
(or any name you choose) in the Name box, and type

=Sheet2:Sheet13!B5
in the Refers To text box.

3.

4.
Press Enter (or click OK).

Now you can use the name Three_D in formulas that contain any of the following

functions: SUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MIN, MINA, MAX, MAXA, PRODUCT,

STDEV, STDEVA, STDEVP, STDEVPA, VAR, VARA, VARP, and VARPA. For example, the formula

=MAX(Three_D) returns the largest value in the three-dimensional range named Three_D.

Because you used relative references in step 3, the definition of the range Three_D changes