Microsoft Office Tutorials and References

In Depth Information

**Using functions that analyze rank and percentile**

TABLE 17-1
The
quart
argument

quart
Returns

0

Lowest value

25th-percentile value

1

Median (50th-percentile) value

2

75th-percentile value

3

Highest value

4

INSIDE OUT
Use MIN, MEDIAN, and MAX

QUARTILE is a powerful function, but if you donâ€™t need the 25th or 75th percentile

values, you will get faster results using other functions, particularly when

working with large data sets. Use the MIN function instead of QUARTILE(
array
, 0), the

MEDIAN function instead of QUARTILE(
array
, 2), and the MAX function instead of

QUARTILE(
array
, 4).

The SMALL and LARGE functions

The SMALL and LARGE functions return the
k
th smallest and
k
th largest values in an input

range; both take the arguments
array
and
k
, where
k
is the position from the largest or

smallest value to the value in the array you want to find. For example, to find the 15th

highest score in the worksheet partially shown in Figure 17-1, you can use the formula

=LARGE($D$2:$D$1001, 15).

The RANK functions

The RANK.AVG and RANK.EQ functions return the ranked position of a particular value

within a set of values; both take the arguments
number
,
ref
, and
order
. If more than one

value in the set has the same rank, the .AVG form of the function returns their average,

while the .EQ form returns the higher value. The
number
argument is the number for which

you want to find the rank,
ref
is the range containing the data set, and
order
(an optional

argument) ranks the number as if it were in a ranking list in an ascending or descending

(the default) order. For example, to find out where the score 1200 falls in the data set in

Figure 17-1, you can use the formula =RANK.AVG(1200, $D$2:$D$1001).