QUARTILE.EXC function - The function returns the quartile of a dataset without values 0 and 1 in Excel

QUARTILE.INC function - The function returns the quartile of a dataset including values 0 and 1 in Excel
QUARTILE function - The function returns the quartile of a dataset in Excel

The following article introduces you to the QUARTILE.EXC function - one of the functions in the group of statistical functions that is very popular in Excel.

QUARTILE.EXC function - The function returns the quartile of a dataset without values 0 and 1 in Excel Picture 1

Description: The function returns the quartile of a dataset, with percentile values from 0 to 1 excluding 0 and 1. The function supports Excel 2010 and later versions.

Syntax: QUARTILE.EXC (array, quart)

Inside:

- array: An array or range containing values that want to find quartiles, a required parameter .

- quart: The position of the quartile to be returned, is a mandatory parameter consisting of 3 values 1, 2 and 3. In addition to these values, the function returns an error value.

Attention:

- If quart is not an integer -> it is truncated to an integer.

- If array is empty -> the function returns the #NUM! Error value .

- If quart ≥ 4 or quart ≤ 0 -> the function returns the #NUM! Error value

- The MIN and MAX, MEDIAN functions return the same value as the QUARTILE.EXC function with corresponding values quart = 0, quart = 4, quart = 2.

For example:

Locate the units as described in the following data table:

QUARTILE.EXC function - The function returns the quartile of a dataset without values 0 and 1 in Excel Picture 2

- Determine the position of the 1st percentile. In the cell to calculate enter the formula : = QUARTILE.EXC (D6: J6, D7)

QUARTILE.EXC function - The function returns the quartile of a dataset without values 0 and 1 in Excel Picture 3

- Press Enter -> 1st percentile is:

QUARTILE.EXC function - The function returns the quartile of a dataset without values 0 and 1 in Excel Picture 4

- The 6th percentile. In the cell to calculate enter the formula: = QUARTILE.EXC (D6: J6,4). Press Enter -> return value is:

QUARTILE.EXC function - The function returns the quartile of a dataset without values 0 and 1 in Excel Picture 5

Here due to the quart value = 6> 4 -> the function returns the #NUM! Error value . The quart value is only in 3 values: 1, 2 and 3.

Above are instructions and some specific examples when using QUARTILE.EXC function in Excel.

Good luck!

Isabella Humphrey

Update 19 May 2020

You should read it

May be interested

MAX function - The function returns the largest value in a set of values in Excel
max function: the function returns the largest value in the set of values. syntax: max (number1, [number2], ...)
STDEV.P function - The function returns the standard deviation based on the whole in Excel
stdev.p: the function returns the standard deviation based on the whole population, ignoring logical values and text. standard deviation is a measure of the dispersion of values against the mean. support functions from excel 2010 onwards. syntax: stdev.p (number1, [number2] ,.
RANK.AVG function - The function returns the rank of a number in a list of numbers in Excel
rank.avg function: the function returns the rank of a number in a list of numbers whose size is in correlation with other values. if multiple values have the same rank -> the function returns the average rank. support functions from execl 2010 version and above
FREQUENCY function - Function that calculates and returns the frequency of occurrences of values in a range in Excel
frequency function: function that calculates and returns the frequency of occurrences of values within a certain range and returns a vertical number array. syntax: frequency (data_array, bins_array)
STDEV.S function - The function returns the standard deviation based on a sample in Excel
stdev.s function: the function returns the standard deviation based on a sample, ignoring logical values and text. standard deviation is a measure of the dispersion of values against the mean. support functions from excel 2010 onwards. syntax: stdev.s (number1, [number2], ...)
PROB function - The function returns the probability that values in a range are between two limits in Excel
prob function: the function returns the probability that values in a range are between two limits. syntax: prob (x_range, prob_range, [lower_limit], [upper_limit])
TREND - The function returns values in a linear trend in Excel
trend: the function returns values in a linear trend. syntax: trend (known_ys, [known_xs], [new_xs], [const])
HARMEAN function - The function returns the harmonic average of a data set in Excel
harmean function: the function returns the harmonic mean of a dataset, the harmonic mean is the reciprocal of the arithmetic mean of the reciprocals. syntax: harmean (number1, [number2], ...)
MAXA function - The function returns the largest value in a set of values including text and logical values in Excel
maxa function: the function returns the largest value in a set of values including text and logical values. syntax: maxa (value1, [value2], ...)
FORECAST function - The function returns a value along a linear trend in Excel
forecast function: the function performs the calculation or prediction of a future value by using current values using linear regression. in excel 2016 this function is replaced by forecast.linear function. syntax: forecast (x, known_ys, known_xs)