The Excel PERCENTILE.EXC function calculates the “kth percentile” for a set of data where k is 0 to 1. A percentile is a value below which a given percentage of values in a data set fall. You can use PERCENTILE.EXC to determine the 90th percentile, the 80th percentile, etc.
Syntax:= PERCENTILE.EXC (array, k)
The PERCENTILE.EXC function syntax has the following arguments:
- array – Data values.
- k – A value between 0 and 1 that represents the k:th percentile.
Example: Let’s look at some Excel PERCENTILE.EXC function examples and explore how to use the PERCENTILE.EXC function as a worksheet function in Microsoft Excel:
Syntax: =PERCENTILE.EXC(B2:B17,C2)
Result: 9.53
Based on the Excel spreadsheet above, the following PERCENTILE.EXC examples would return:
Syntax: =PERCENTILE.EXC(B2:B17,C3)
Result: 8.8
Syntax: =PERCENTILE.EXC(B2:B17,C4)
Result: 8.5
Syntax: =PERCENTILE.EXC(B2:B17,C5)
Result: 8.42
Syntax: =PERCENTILE.EXC(B2:B17,C6)
Result: 8.1
Syntax: =PERCENTILE.EXC(B2:B17,C7)
Result: 7.05
Syntax: =PERCENTILE.EXC(B2:B17,C8)
Result: #NUM!
Note:
- If the column is empty, BLANK () is returned.
- If k <= 0, or k >= 1, it is out of range and an error is returned.
- If k is non-numeric, an error is returned.
- If k is not a multiple of 1/(n + 1), PERCENTILE.EXC will interpolate to determine the value at the kth percentile.
PERCENTILE.EXC will interpolate when the value for the specified percentile is between two values in the array. If it cannot interpolate for the k percentile specified, an error is returned.
