Microsoft Office Tutorials and References

In Depth Information

**Syntax**

Syntax

=PERCENTILE.INC(array,k)

=PERCENTILE.EXC(array,k)

The Excel 2007 PERCENTILE function is included in Excel 2013 for compat-

ibility with older versions. In Excel 2013, the PERCENTILE.INC function is

equivalent to PERCENTILE. The new PERCENTILE.EXC function made its de-

but in Excel 2010.

The PERCENTILE.INC function returns the kth percentile of values in a

range. You can use this function to establish a threshold of acceptance. For

example, you can decide to examine candidates who score above the 90th per-

centile. This function takes the following arguments:

•
array

array
—
This is the array or range of data that defines relative

standing. If arrayis empty, PERCENTILE.INC returns a #NUM! error.

•
k
—
This is the percentile value in the range 0
–
1, inclusive. If kis

nonnumeric, PERCENTILE.INC returns a #VALUE! error. If kis less

than 0 or greater than 1, PERCENTILE.INC returns a #NUM! error. If k

is not a multiple of 1 / (n
−
1), PERCENTILE.INC interpolates to de-

termine the value at the kth percentile. In this case, n is the number of

items in the array.

In
Figure 14.9
, 33 employees are in column A. Their ratings on an annual review

are shown in column B. The formula in cell F3, =PERCENTILE.INC(B2:B34,F2),

calculates the level of the 81st percentile. After you determine the particu-

lar percentile, you can mark all the qualifying employees by using the for-

mula =B2>=$F$3 in cells C2:C33.