Microsoft Office Tutorials and References
In Depth Information
Entering Formulas into Your Worksheets
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
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.
10. Highlight TestScores, and then press Tab.
11. Type a closing parenthesis and then press Enter.