fx
FunctionPedia
Quick Answer

PERCENTILE Function

Returns the value at a given percentile (k from 0 to 1, inclusive).

✓ Excel✓ Google SheetsExcel All versions

Syntax

PERCENTILE
(array, k)

Parameters

ParameterDescriptionRequired
arrayThe range or array of numeric values.Required
kThe percentile value, a number between 0 and 1 inclusive (e.g. 0.5 for the median).Required

Basic Example

Median (50th percentile) of four values

=PERCENTILE({1,2,3,4}, 0.5)
Result2.5

Using the inclusive method the rank is 1 + 0.5*(4-1) = 2.5, interpolated between 2 and 3 to give 2.5.

Advanced Examples

Example 1: 25th percentile with exact position

Five evenly spaced values

When the computed rank lands on an integer, the value at that position is returned.

=PERCENTILE({1,2,3,4,5}, 0.25)
Result: 2
Rank = 1 + 0.25*(5-1) = 2, so the 2nd value, 2, is returned.

Example 2: 75th percentile with interpolation

Fractional rank between two values

A non-integer rank is linearly interpolated.

=PERCENTILE({1,2,3,4}, 0.75)
Result: 3.25
Rank = 1 + 0.75*(4-1) = 3.25; interpolate 25% from the 3rd to the 4th value: 3 + 0.25*(4-3) = 3.25.

How PERCENTILE Works

PERCENTILE uses the inclusive (INC) method. It calculates a rank position as 1 + k*(n-1), where n is the count of values, then interpolates linearly between the surrounding sorted values. k must be between 0 and 1 inclusive; k=0 returns the minimum and k=1 returns the maximum. This function is equivalent to PERCENTILE.INC. Text and logical values in the array are ignored.

1
Select the result cell
Click where you want the percentile.
2
Enter the formula
Type =PERCENTILE(range, k) with k between 0 and 1.
3
Press Enter
The interpolated percentile value is returned.

Important Notes & Limitations

  • k must be between 0 and 1 inclusive; values outside this range cause #NUM!.

  • May linearly interpolate between data points, returning a value not present in the data.

  • Ignores text and logical values in the array.

Common Errors & Fixes

#NUM! errork is less than 0 or greater than 1, or the array is empty.

Fix: Use a k value between 0 and 1 and ensure the range contains numbers.

Unexpected decimal resultInterpolation between two data points is by design.

Fix: This is expected; the percentile need not be an actual data value.

Download Practice File

Practice PERCENTILE with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the PERCENTILE function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

Is PERCENTILE the same as PERCENTILE.INC?
Yes. PERCENTILE is the legacy name and PERCENTILE.INC was added in Excel 2010; both use the inclusive method.
Why might the result not be in my data?
The inclusive method interpolates between the two nearest values when the rank is fractional.
What does k = 0 or k = 1 return?
k = 0 returns the minimum value and k = 1 returns the maximum value.