The QUARTILE.EXC function in Google Sheets is a powerful statistical tool used for identifying the quartiles of a dataset. It provides insights into data distribution, allowing users to understand the spread and central tendency of data points, making it essential for statistical analysis and data visualization.
Syntax
QUARTILE.EXC(data, quart)
- data: A range of cells or an array of numeric values representing the dataset.
- quart: An integer value that specifies which quartile to return. This must be one of the following: 1 (first quartile), 2 (second quartile or median), or 3 (third quartile).
Example #1
QUARTILE.EXC(A1:A10, 1)
This function calculates the first quartile (25th percentile) of the values in the range A1 to A10. If A1 to A10 contains values 10, 20, 30, 40, 50, the result would be 17.5, representing the value below which 25% of the data falls.
Example #2
QUARTILE.EXC(B1:B10, 2)
Here, the function returns the second quartile (median) for the dataset in B1 to B10. If the range includes values of 5, 15, 25, 35, 45, the result would be 20, which is the median value in this set.
Example #3
QUARTILE.EXC(C1:C10, 3)
This function determines the third quartile (75th percentile) for the values in the range C1 to C10. If C1 to C10 contains values like 2, 4, 6, 8, 10, the result would be 7.5, indicating that 75% of the dataset is below this value.
Error handling
- VALUE! This error occurs if the quartile parameter is not a valid integer (should be 1, 2, or 3).
- REF! This error indicates that the data range provided is invalid or refers to a deleted range.
- NUM! This is returned when the specified quartile is less than 1 or greater than 3, as these values are not supported by the function.