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).             Search JabSto ::

Custom Search