Microsoft Office Tutorials and References
In Depth Information
Using functions that analyze rank and percentile
You can find the SAT Scores.xlsx file with the other examples on the companion website.
Both forms of the PERCENTRANK function take the arguments array , x , and significance .
The array argument specifies the input range (which is $D$2:$D$1001, in our example), and
x specifies the value whose rank you want to obtain. The significance argument, which is
optional, indicates the number of digits of precision you want; if this argument is omitted,
results are rounded to three digits ( 0.xxx or xx.x% ).
Figure 17-1 The PERCENTRANK.INC function determines where a value stands in a population.
The PERCENTILE and QUARTILE functions
You use the PERCENTILE.INC and PERCENTILE.EXC functions to find the member of a
data set that stands at a specified percentile rank; they both take the arguments array ,
and k . The .INC (inclusive) form of this function includes the entire data set, and the .EXC
(exclusive) form eliminates rankings of 0% and 100%. You must express the percentile k
as a decimal fraction from 0 to 1. For example, to find out which score in the worksheet
partially shown in Figure 17-1 represents the 86th percentile, you can use the formula
You can also use the AGGREGATE function to apply these functions with options to ignore
hidden rows and error values. For more information, see “The AGGREGATE function” in
The QUARTILE functions, which take the arguments array , and quart , work much like the
PERCENTILE functions, except they return the values at the lowest, 25th, median, 75th, or
highest percentile in the input set. The array argument specifies the input range. The quart
argument specifies the value to be returned, as shown in Table 17-1.