Chapter 10: Introducing Formulas and Functions
205
Pasting range names into formulas ..........................................................................
If your formula uses named cells or ranges, you can either type the name in place of the address, or
choose the name from a list and have Excel insert the name for you automatically. Two ways to
insert a name into a formula are available:l Select the name from the drop-down list. To use this method, you must know at least
the first character of the name. When you’re entering the formula, type the first character
and then select the name from the drop-down list.l (^) Press F3. This action displays the Paste Name dialog box. Select the name from the list
and then click OK (or just double-click the name). Excel will enter the name into your
formula. If no names are defined, pressing F3 has no effect.
Figure 10.3 shows an example. The worksheet contains two defined names: Expenses and
Sales. The Paste Name dialog box is being used to insert a name (Sales) into the formula being
entered in cell B10.
- Select 1 - AVERAGE and then press Tab. Excel inserts 1 , which is the code for calculating
the average. - Type a comma to separate the next argument.
- When Excel displays a list of items for the AGGREGATE function’s second argument, select 2
- Ignore Error Values and then press Tab.
- Type a comma to separate the third argument (the range of test scores).
- Type a T to get a list of functions and names that begin with T. You’re looking for
TestScores, so narrow it down a bit by typing the second character (e). - Highlight TestScores and then press Tab.
- Type a closing parenthesis and then press Enter.
The completed formula is
=AGGREGATE(1,2,TestScores)Formula Autocomplete includes the following items (and each type is identified by a separate icon):l Excel built-in functions
l User-defined functions (functions defined by the user through VBA or other methods)
l Defined names (named using the Formulas ➪ Defined Names ➪ Define Name command)
l Enumerated arguments that use a value to represent an option (only a few functions use such
arguments, and AGGREGATE is one of them)
l Table structure references (used to identify portions of a table)