Part II: Working with Formulas and Functions
Getting the largest or smallest value
Figure 16.8 shows the average low temperature by month for the city of Marietta, Georgia.
We want to determine which months have the highest and lowest average temperature. The
first formula will find the maximum average low temperature.
=MAX(C3:C14)
FIGURE 16.8
Average low temperatures by month
The next formula will return the month that corresponds to the temperature found in the
previous formula:
=INDEX(B3:B14,MATCH(E5,C3:C14,FALSE),1)
Excel provides two functions for finding the largest and smallest values from a range: MAX
and MIN. Both formulas accept up to 255 arguments. Our data is in C3:C14, and that is the
range passed into MAX and MIN. MAX returns 70, the largest value in the range, and MIN
returns 33, the smallest.
See Chapter 14 for more information on the MATCH and INDEX functions.
To determine to which months those temperatures relate, we start with the INDEX func-
tion. The range passed into INDEX is the list of months in B3:B14. The second argument to
INDEX is a MATCH function, which returns the position of the lookup value in a list. When
we match 70 to our list of temperatures, MATCH returns 7 because 70 is the seventh item
