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
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.
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