Datasheet

The COUNTIF function counts the number of cells in a range that meet a
condition. The condition in this case is that any single value in the range
is greater than (>) the average of the range. The formula in cell D7 is
=COUNTIF(B5:B25, “>” & AVERAGE(B5:B25)). The AVERAGE function
is evaluated first, and then the COUNTIF function is evaluated using the
returned value from the nested function used as an argument.
Nested functions are best entered directly. The Insert Function dialog box
does not make it easy to enter a nested function. Try one out. In this exam-
ple, you use the AVERAGE function to find the average of the largest values
from two sets of numbers. The nested function in this example is MAX. You
enter the MAX function twice within the AVERAGE function:
1. Enter a few different numbers in one column.
2. Enter a few different numbers in a different column.
3. Click an empty cell where you want the result to appear.
4. Enter
=AVERAGE( to start the function entry.
5. Enter
MAX(.
6. Click the first cell in the second set of numbers, press the mouse
button, and drag over all the cells of the first set.
The address of this range enters into the MAX function.
7. Enter a closing parenthesis to end the first MAX function.
8. Enter a comma (
,).
9. Once again, enter
MAX(.
10. Click the first cell in the second set of numbers, press the mouse
button, and drag over all the cells of the second set.
The address of this range enters into the MAX function.
11. Enter a closing parenthesis to end the second MAX function.
12. Enter a
).
This ends the AVERAGE function.
13. Press Enter.
Figure 1-27 shows the result of your nested function. Cell C14 has this for-
mula:
=AVERAGE(MAX(B4:B10),MAX(D4:D10)).
When using nested functions, the outer function is preceded with an equal
sign (=) if it is the beginning of the formula. Any nested functions are
not pre-
ceded with an equal sign.
35
Chapter 1: Tapping into Formula and Function Fundamentals
05_046555 ch01.qxp 12/26/06 7:54 PM Page 35