Datasheet

34
Part I: Putting the Fun in Functions
You do not have to use the Insert Function dialog box to enter functions into
cells. It is there for convenience. As you become familiar with certain func-
tions that you use repeatedly, you may find it faster to just type the function
directly into the cell.
Nesting functions
Nesting is something a bird does, isn’t it? Well, a bird expert would know the
answer to that one, but we do know how to nest Excel functions. A nested
function is tucked inside another function, as one of its arguments. Nesting
functions let you return results you would have a hard time getting to other-
wise. (Nested functions are used in examples in various places in the book.
The COUNTIF, AVERAGE, and MAX functions are discussed in Chapter 9.)
Figure 1-26 shows the daily closing price for the S&P 500, for the month of
August 2009. A possible analysis is to see how many times the closing price
was higher than the average for the month. Therefore, the average needs to
be calculated first, before you can compare any single price. By embedding
the AVERAGE function inside another function, the average is first calculated.
When a function is nested inside another, the inner function is calculated first.
Then that result is used as an argument for the outer function.
Figure 1-26:
Nesting
functions.
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
05_568163-ch01.indd 3405_568163-ch01.indd 34 4/2/10 1:37 PM4/2/10 1:37 PM