PERCENTILE.EXC function

The 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.