Microsoft Office Tutorials and References

In Depth Information

**Entering Formulas into Your Worksheets**

FIGURE 15.2

Excel displays a drop-down list when you enter a formula.

Using Formula AutoComplete

The Formula AutoComplete feature makes entering formulas easier than ever. Here’s a quick

walkthrough that demonstrates how it works. The goal is to create a formula that uses the
AGGREGATE

function to calculate the average value in a range that I named
TestScores
. The
AVERAGE
function

will not work in this situation because the range contains an error value.

1.
Select the cell that will hold the formula, and type an equal sign (
) to signal the start
=

of a formula.

2.
Type the letter
You get a list of functions and names that begin with
A. This feature is

not case sensitive, so you can use either uppercase or lowercase characters.

3.
Scroll through the list, or type
G to narrow down the choices.

4.
When AGGREGATE is highlighted, press Tab to select it.
Excel adds the opening

parenthesis and displays another list that contains options for the ﬁ rst argument for
AGGREGATE
,

as shown in Figure 15.2.

5.
Select 1 - AVERAGE and then press Tab.
Excel inserts
1
, which is the code for calculating

the average.

6.
Type a comma to separate the next argument.

7.
When Excel displays a list of items for the AGGREGATE function’s second argument,

select 2 - Ignore Error Values and then press Tab.

8.
Type a comma to separate the third argument (the range of test scores).

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

15

10.
Highlight TestScores, and then press Tab.

11.
Type a closing parenthesis and then press Enter.

Continues