QUARTILE.EXC function

The QUARTILE.EXC function returns value nearest to a given quartile of a dataset, exclusive of 0 and 4.

Parts of a QUARTILE.EXC function

QUARTILE.EXC(data, quartile_number)

Part Description
data The array or range containing the dataset to consider.
quartile_number This is which quartile value to return
  • 1 returns the value in data closest to the first quartile (25% mark)
  • 2 returns the value in data closest to the median (50% mark)
  • 3 returns the value in data closest to the third quartile (75% mark)

Sample formulas

QUARTILE.EXC(A2:A100, 3)

QUARTILE.EXC(A2:A100, B2)

Notes

  • If the quartile_number is not an integer, the number is truncated. 
  • The quartile_number must be between 0 and 4, exclusive.
  • Different from QUARTILE.INC, QUARTILE.EXC calculates quartile based on a quartile range exclusive of 0th and 4th quartile.

Examples

  A
1 data
2 6
3 7
4 15
5 36
6 39
7 40
8 41
9 42
10 43
11 47
12 49

 

  A B C
1 Quartile Result Formula
2 1 15 =QUARTILE.EXC(A2:A12, 1)
3 2 40 =QUARTILE.EXC(A2:A12, 2)
4 3 43 =QUARTILE.EXC(A2:A12, 3)

Related functions

  • PERCENTILE: Returns the value at a given percentile of a dataset.
  • PERCENTILE.EXC: The PERCENTILE.EXC function returns the value at a given percentile of a dataset, exclusive of 0 and 1.
  • MEDIAN: Returns the median value in a numeric dataset.
  • QUARTILE: Returns a value nearest to a specified quartile of a dataset.
Was this helpful?
How can we improve it?