PERCENTILE

 Excel provides several functions to calculate percentiles, which are useful for understanding the distribution of data. Below are 10 expanded examples of percentile-related functions, including sample data and formulas.

1. PERCENTILE.INC

• Description: Returns the k-th percentile of values in a range, where k is between 0 and 1 (inclusive).

• Formula: =PERCENTILE.INC(range, k)

• Example:

  • Data: A1:A10 = 10, 20, 30, 40, 50, 60, 70, 80, 90, 100

  • Formula: =PERCENTILE.INC(A1:A10, 0.9)

  • Result: 91 (90th percentile)

2. PERCENTILE.EXC

• Description: Returns the k-th percentile of values in a range, where k is between 0 and 1 (exclusive).

• Formula: =PERCENTILE.EXC(range, k)

• Example:

  • Data: A1:A10 = 10, 20, 30, 40, 50, 60, 70, 80, 90, 100

  • Formula: =PERCENTILE.EXC(A1:A10, 0.9)

  • Result: 91.8 (90th percentile)

3. QUARTILE.INC

• Description: Returns the quartile of a data set, where quart is between 0 and 4 (inclusive).

• Formula: =QUARTILE.INC(range, quart)

• Example:

  • Data: A1:A10 = 10, 20, 30, 40, 50, 60, 70, 80, 90, 100

  • Formula: =QUARTILE.INC(A1:A10, 3)

  • Result: 75 (3rd quartile, 75th percentile)

4. QUARTILE.EXC

• Description: Returns the quartile of a data set, where quart is between 1 and 3 (exclusive).

• Formula: =QUARTILE.EXC(range, quart)

• Example:

  • Data: A1:A10 = 10, 20, 30, 40, 50, 60, 70, 80, 90, 100

  • Formula: =QUARTILE.EXC(A1:A10, 3)

  • Result: 77.5 (3rd quartile, 75th percentile)

5. PERCENTRANK.INC

• Description: Returns the rank of a value in a data set as a percentage (inclusive).

• Formula: =PERCENTRANK.INC(range, x)

• Example:

  • Data: A1:A10 = 10, 20, 30, 40, 50, 60, 70, 80, 90, 100

  • Formula: =PERCENTRANK.INC(A1:A10, 50)

  • Result: 0.444 (44.4%)

6. PERCENTRANK.EXC

• Description: Returns the rank of a value in a data set as a percentage (exclusive).

• Formula: =PERCENTRANK.EXC(range, x)

• Example:

  • Data: A1:A10 = 10, 20, 30, 40, 50, 60, 70, 80, 90, 100

  • Formula: =PERCENTRANK.EXC(A1:A10, 50)

  • Result: 0.4 (40%)

7. SMALL

• Description: Returns the k-th smallest value in a data set.

• Formula: =SMALL(range, k)

• Example:

  • Data: A1:A10 = 10, 20, 30, 40, 50, 60, 70, 80, 90, 100

  • Formula: =SMALL(A1:A10, 3)

  • Result: 30 (3rd smallest value)

8. LARGE

• Description: Returns the k-th largest value in a data set.

• Formula: =LARGE(range, k)

• Example:

  • Data: A1:A10 = 10, 20, 30, 40, 50, 60, 70, 80, 90, 100

  • Formula: =LARGE(A1:A10, 3)

  • Result: 80 (3rd largest value)

9. MEDIAN

• Description: Returns the median (50th percentile) of a data set.

• Formula: =MEDIAN(range)

• Example:

  • Data: A1:A10 = 10, 20, 30, 40, 50, 60, 70, 80, 90, 100

  • Formula: =MEDIAN(A1:A10)

  • Result: 55 (median value)

10. RANK

• Description: Returns the rank of a number in a list of numbers.

• Formula: =RANK(number, range, [order])

• Example:

  • Data: A1:A10 = 10, 20, 30, 40, 50, 60, 70, 80, 90, 100

  • Formula: =RANK(50, A1:A10, 0)

  • Result: 6 (rank of 50 in descending order)

Summary Table

Function	Description	Example Formula	Example Result
PERCENTILE.INC	Inclusive percentile	=PERCENTILE.INC(A1:A10, 0.9)	91
PERCENTILE.EXC	Exclusive percentile	=PERCENTILE.EXC(A1:A10, 0.9)	91.8
QUARTILE.INC	Inclusive quartile	=QUARTILE.INC(A1:A10, 3)	75
QUARTILE.EXC	Exclusive quartile	=QUARTILE.EXC(A1:A10, 3)	77.5
PERCENTRANK.INC	Inclusive percent rank	=PERCENTRANK.INC(A1:A10, 50)	0.444
PERCENTRANK.EXC	Exclusive percent rank	=PERCENTRANK.EXC(A1:A10, 50)	0.4
SMALL	k-th smallest value	=SMALL(A1:A10, 3)	30
LARGE	k-th largest value	=LARGE(A1:A10, 3)	80
MEDIAN	Median value	=MEDIAN(A1:A10)	55
RANK	Rank of a number	=RANK(50, A1:A10, 0)	6

These functions are powerful tools for statistical analysis in Excel, allowing you to understand the distribution and ranking of your data.