QUARTILE

 Excel provides several functions to calculate quartiles, which are useful for dividing a dataset into four equal parts. Below are the quartile functions in Excel, along with examples and formulas:

1. QUARTILE.INC (Inclusive Quartile)

• Description: Returns the quartile of a dataset, including the 0th and 4th quartiles.

• Syntax: =QUARTILE.INC(array, quart)

• Arguments:

  • array: The range of data.

  • quart: The quartile value (0, 1, 2, 3, or 4).

• Example:

  • Data: A1:A5 = 10, 20, 30, 40, 50

  • Formula: =QUARTILE.INC(A1:A5, 1)

  • Result: 22.5 (First quartile, 25th percentile)

2. QUARTILE.EXC (Exclusive Quartile)

• Description: Returns the quartile of a dataset, excluding the 0th and 4th quartiles.

• Syntax: =QUARTILE.EXC(array, quart)

• Arguments:

  • array: The range of data.

  • quart: The quartile value (1, 2, or 3).

• Example:

  • Data: A1:A5 = 10, 20, 30, 40, 50

  • Formula: =QUARTILE.EXC(A1:A5, 1)

  • Result: 20 (First quartile, 25th percentile)

3. PERCENTILE.INC (Inclusive Percentile)

• Description: Returns the k-th percentile of a dataset, including 0% and 100%.

• Syntax: =PERCENTILE.INC(array, k)

• Arguments:

  • array: The range of data.

  • k: The percentile value (0 to 1).

• Example:

  • Data: A1:A5 = 10, 20, 30, 40, 50

  • Formula: =PERCENTILE.INC(A1:A5, 0.25)

  • Result: 22.5 (25th percentile)

4. PERCENTILE.EXC (Exclusive Percentile)

• Description: Returns the k-th percentile of a dataset, excluding 0% and 100%.

• Syntax: =PERCENTILE.EXC(array, k)

• Arguments:

  • array: The range of data.

  • k: The percentile value (0 to 1, exclusive).

• Example:

  • Data: A1:A5 = 10, 20, 30, 40, 50

  • Formula: =PERCENTILE.EXC(A1:A5, 0.25)

  • Result: 20 (25th percentile)

5. MEDIAN (Middle Quartile)

• Description: Returns the median (2nd quartile) of a dataset.

• Syntax: =MEDIAN(array)

• Arguments:

  • array: The range of data.

• Example:

  • Data: A1:A5 = 10, 20, 30, 40, 50

  • Formula: =MEDIAN(A1:A5)

  • Result: 30 (Median, 50th percentile)

6. MIN (0th Quartile)

• Description: Returns the minimum value in a dataset (0th quartile).

• Syntax: =MIN(array)

• Arguments:

  • array: The range of data.

• Example:

  • Data: A1:A5 = 10, 20, 30, 40, 50

  • Formula: =MIN(A1:A5)

  • Result: 10 (Minimum value)

7. MAX (4th Quartile)

• Description: Returns the maximum value in a dataset (4th quartile).

• Syntax: =MAX(array)

• Arguments:

  • array: The range of data.

• Example:

  • Data: A1:A5 = 10, 20, 30, 40, 50

  • Formula: =MAX(A1:A5)

  • Result: 50 (Maximum value)

8. QUARTILE (Legacy Function)

• Description: Legacy function for backward compatibility. Similar to QUARTILE.INC.

• Syntax: =QUARTILE(array, quart)

• Arguments:

  • array: The range of data.

  • quart: The quartile value (0, 1, 2, 3, or 4).

• Example:

  • Data: A1:A5 = 10, 20, 30, 40, 50

  • Formula: =QUARTILE(A1:A5, 1)

  • Result: 22.5 (First quartile, 25th percentile)

9. PERCENTILE (Legacy Function)

• Description: Legacy function for backward compatibility. Similar to PERCENTILE.INC.

• Syntax: =PERCENTILE(array, k)

• Arguments:

  • array: The range of data.

  • k: The percentile value (0 to 1).

• Example:

  • Data: A1:A5 = 10, 20, 30, 40, 50

  • Formula: =PERCENTILE(A1:A5, 0.25)

  • Result: 22.5 (25th percentile)

10. RANK (Ranking Data)

• Description: Returns the rank of a number in a dataset, which can be useful for quartile analysis.

• Syntax: =RANK(number, ref, [order])

• Arguments:

  • number: The number whose rank you want to find.

  • ref: The range of data.

  • order: Optional. 0 for descending, 1 for ascending.

• Example:

  • Data: A1:A5 = 10, 20, 30, 40, 50

  • Formula: =RANK(30, A1:A5, 0)

  • Result: 3 (Rank of 30 in descending order)

Summary Table of Quartile Functions:

Function	Description	Example Formula	Example Result
QUARTILE.INC	Inclusive quartile	=QUARTILE.INC(A1:A5, 1)	22.5
QUARTILE.EXC	Exclusive quartile	=QUARTILE.EXC(A1:A5, 1)	20
PERCENTILE.INC	Inclusive percentile	=PERCENTILE.INC(A1:A5, 0.25)	22.5
PERCENTILE.EXC	Exclusive percentile	=PERCENTILE.EXC(A1:A5, 0.25)	20
MEDIAN	Median (2nd quartile)	=MEDIAN(A1:A5)	30
MIN	Minimum (0th quartile)	=MIN(A1:A5)	10
MAX	Maximum (4th quartile)	=MAX(A1:A5)	50
QUARTILE	Legacy quartile function	=QUARTILE(A1:A5, 1)	22.5
PERCENTILE	Legacy percentile function	=PERCENTILE(A1:A5, 0.25)	22.5
RANK	Rank of a number in a dataset	=RANK(30, A1:A5, 0)	3

These functions are essential for statistical analysis in Excel, allowing you to understand the distribution and spread of your data