# How to Use PERCENTILE.EXC function in Google Sheets in 2020?

PERCENTILE.EXC functionThe PERCENTILE.EXC function returns the value at a given percentile of a dataset, exclusive of 0 and 1.

Parts of a PERCENTILE.EXC function

PERCENTILE.EXC(data, percentile)

Part
Description

data
The array or range containing the dataset to consider.

percentile
The percentile whose value within data will be calculated and returned.

Sample formulas

PERCENTILE.EXC(A2:A100,0.33)

PERCENTILE.EXC(A2:A100,A2)

Notes

The value returned by PERCENTILE.EXC is not necessarily a member of data as this function interpolates between values to calculate the alpha percentile.
The percentile must be between 0 and 1, exclusive.
Different from PERCENTILE.INC, PERCENTILE.EXC calculates percentile based on a percentile range exclusive of 0 and 1.

Examples

A
B

1
data
percentile

2
15
0.15

3
20
0.4

4
35
0.5

5
40
0.8

6
50
0.9

A
B
C
D

1
Percentile
Result
Formula
Explanation

2
0.15
#NUM!
=PERCENTILE.EXC(A2:A6, B2)
Returns #NUM! error because it can’t interpolate.

3
0.4
26
=PERCENTILE.EXC(A2:A6, B3)
Executes as intended.

4
0.5
35
=PERCENTILE.EXC(A2:A6, B4)
Executes as intended.

5
0.8
48
=PERCENTILE.EXC(A2:A6, B5)
Executes as intended.

6
0.9
#NUM!
=PERCENTILE.EXC(A2:A6, B6)
Returns #NUM! error because it can’t interpolate.

Related functions

PERCENTILE: Returns the value at a given percentile of a dataset.
QUARTILE: Returns a value nearest to a specified quartile of a dataset.
MEDIAN: Returns the median value in a numeric dataset.
QUARTILE.EXC: The QUARTILE.EXC function returns value nearest to a given quartile of a dataset, exclusive of 0 and 4.

Categories Uncategorized