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

=PERCENTILE.INC($D$2:$D$1001, 0.86).

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

Chapter 14.

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.