How to Use the QUARTILE and PERCENTILE Functions in Google Sheets to Segment Datasets into Statistical Ranges

When analyzing large datasets like employee salaries, standardized test scores, or sales transaction amounts, looking only at averages or medians provides an incomplete picture of data distribution. Statistical segmentation allows financial analysts and data scientists to understand data dispersion and divide records into performance tiers. In Google Sheets, the QUARTILE and PERCENTILE functions compute exact cutoff values to segment numerical distributions into balanced mathematical brackets.

Syntax and Parameters of the QUARTILE Function

The QUARTILE function calculates the boundary value at a designated quarter of your dataset:

=QUARTILE(data, quartile_number)

• data: The array or contiguous cell range containing numeric values (e.g., B2:B100).
• quartile_number: An integer representing which quartile cutoff to calculate:
  • 0: Minimum value in the dataset (same as MIN).
  • 1: First quartile (25th percentile) – 25% of data falls below this threshold.
  • 2: Second quartile (50th percentile) – the exact median (same as MEDIAN).
  • 3: Third quartile (75th percentile) – 75% of data falls below this threshold.
  • 4: Maximum value in the dataset (same as MAX).

Syntax and Parameters of the PERCENTILE Function

While QUARTILE restricts segmentation to 25% increments, PERCENTILE calculates cutoffs for any arbitrary fractional rank between 0 and 1:

=PERCENTILE(data, percentile_value)

• percentile_value: The desired rank expressed as a decimal (e.g., 0.90 for the 90th percentile) or percentage (90%).

Step-by-Step Example: Segmenting Sales Rep Performance

Suppose you have quarterly revenue numbers for 50 sales representatives stored in cells C2:C51. You want to establish four compensation tiers based on quartiles:

1. To find the 25th percentile threshold (Tier 1 cutoff):

   =QUARTILE(C2:C51, 1)

2. To find the Median sales revenue (Tier 2 cutoff):

   =QUARTILE(C2:C51, 2)

3. To find the 75th percentile benchmark (Tier 3 cutoff):

   =QUARTILE(C2:C51, 3)

4. To identify the top 5% elite performers (95th percentile threshold):

   =PERCENTILE(C2:C51, 0.95)

Calculating the Interquartile Range (IQR) for Outlier Detection

In statistics, the Interquartile Range (IQR) measures the spread of the middle 50% of your data and serves as the foundation for detecting statistical outliers in box-plot distributions:

=QUARTILE(C2:C51, 3) - QUARTILE(C2:C51, 1)

Any data point located 1.5 times the IQR below Q1 or above Q3 is mathematically categorized as an extreme outlier.

Handling Missing Values and Edge Cases

• Text Ignored: Non-numeric cells and blank spaces in the target range are automatically skipped during calculation.
• Interpolation: If the exact percentile rank falls between two discrete numbers, Google Sheets automatically performs linear interpolation to calculate a precise smooth threshold.
• Invalid Parameters: Passing a quartile number other than 0, 1, 2, 3, or 4 generates a #NUM! calculation error.