Mastering the PERCENTILE Function in Google Sheets: Calculate Percentile Ranks with Ease

Maximizing the potential of your data can feel challenging, but with the right tools, it becomes much simpler. Google Sheets provides powerful functions like PERCENTILE to help you analyze and interpret data effectively.

Whether you're identifying thresholds, tracking performance, or comparing results, the PERCENTILE function empowers you to extract meaningful insights from your data.

In this article, we’ll walk you through how to use the PERCENTILE function for accurate data analysis. You’ll discover how to calculate percentiles, explore real-world examples, and apply best practices to improve your workflow. Let’s dive in and make data-driven decisions easier with Google Sheets!

The Purpose of the PERCENTILE Function in Google Sheets

The PERCENTILE function in Google Sheets is a powerful tool for analyzing data distributions and identifying specific data points within a dataset. It helps you determine the value below which a given percentage of data falls, making it useful for tasks like calculating medians, identifying outliers, and comparing data points. 

By applying the PERCENTILE function, you can quickly assess the spread and performance of your data. Whether you’re evaluating exam scores, sales figures, or survey results, this function provides insights that help you make data-driven decisions. 

Its flexibility allows you to find key thresholds, such as the top 10% or the middle 50%, offering deeper visibility into your dataset's structure and performance.

Understanding the PERCENTILE Function: Syntax and Examples

Understanding how to use the PERCENTILE function is essential for effective data analysis in Google Sheets. In the following section, we’ll break down its syntax and show practical examples to help you apply this function in real-world scenarios, making your data analysis more accurate and insightful.

PERCENTILE

The PERCENTILE function in Google Sheets helps you determine the value below which a given percentage of your data lies. It’s a valuable tool for analyzing distributions, dividing your data into segments based on specified percentages. 

Syntax of PERCENTILE

The syntax of the PERCENTILE function in Google Sheets is:

=PERCENTILE(data, percentile)

Let’s break down what these parameters represent:

• data: The range or array containing the dataset to analyze.
• percentile: The percentile to calculate, specified as a decimal between 0 and 1.

Both arguments must calculate and return the value below which a specified percentage of data points fall.

Example of PERCENTILE

Let’s say you have a list of monthly sales data for each representative in column C, with each row representing the total sales for a specific rep. You want to identify the sales figure representing the 25th percentile, showing the point below which 25% of your sales reps have monthly sales.

Formula explanation:

=PERCENTILE(C3:C12, 0.25)

• C3:C12: Refers to the range containing monthly sales data for all products.
• 0.25: Represents the 25th percentile, or the point below which 25% of the sales data lies.

In cell E3, the formula will return 36050, identifying products in the bottom 25% of sales. This allows your business to focus on improving performance for these lower-performing items.

PERCENTILE.INC

In Google Sheets, PERCENTILE is the same as PERCENTILE.INC, used for backward compatibility. Both functions calculate the value below which a specified percentage of data falls, including the percentile within the dataset, and are often referenced interchangeably.

PERCENTILE.EXC

The PERCENTILE.EXC function in Google Sheets calculates the k-th percentile of a dataset, excluding the percentile value itself. It’s used to find the rank of a value between 0 and 1, excluding boundary values, providing a refined measure of data distribution compared to PERCENTILE.INC.

Syntax of PERCENTILE.EXC

The syntax of the PERCENTILE.EXC function in Google Sheets is:

=PERCENTILE.EXC(data, percentile)

Let’s break down what these parameters represent:

• data: The range or array containing the dataset to analyze.
• percentile: A decimal between 0 and 1 that specifies which percentile to calculate, excluding the boundary values.

Both arguments must calculate the percentile while excluding extreme values from the dataset.

Example of PERCENTILE.EXC

Let’s say again, you have a list of monthly sales data. You want to identify the sales figure representing the 25th percentile, excluding the boundary values.

Formula Explanation:

=PERCENTILE.EXC(C3:C12, 0.25)

• C3:C12: Refers to the range containing sales data.
• 0.25: Represents the 25th percentile, or the point below which 25% of the sales data lies, excluding extreme values.

In cell E3, the formula will return 34475, slightly different from the PERCENTILE function result. This refined result excludes boundary values, offering a more focused view of lower-performing products.

Master the PERCENTILE Function with Our Template!

Get all the examples from the article in this ready-to-use template. Download it now and start calculating percentile ranks in Google Sheets effortlessly.

Get Your Free Template Now!

Mastering the PERCENTILE Function with Real-World Examples

Understanding the PERCENTILE function in Google Sheets is crucial for analyzing data distributions accurately. By calculating percentiles, you can pinpoint key data points like the median or outliers and gain insights. Let’s dive into real-world examples to see how this function can be applied effectively.

Finding the 50th Percentile (Median) with PERCENTILE.INC and PERCENTILE.EXC

The 50th percentile, also known as the median, represents the midpoint in your dataset where half of the values are below and half are above. Using PERCENTILE.INC and PERCENTILE.EXC in Google Sheets, you can easily calculate this value to understand the central tendency of your data.

Example of PERCENTILE.INC

Let’s say you have a list of monthly sales figures for a team of 10 sales representatives in column C, and you want to calculate the median sales value to understand the middle point of performance.

Formula explanation:

=PERCENTILE.INC(C2:C12, 0.5)

• C2:C12: Refers to the range containing the sales data for all 10 sales representatives.
• 0.5: Represents the 50th percentile or the median value in the dataset.

In column E2, you will find that the formula will return 43900, which is the team's median (50th percentile) sales value. 

Example of PERCENTILE.EXC

Using the monthly sales figures, now let's calculate the median sales value, excluding boundary values, for a clearer view of mid-level performance.

Formula Explanation:

=PERCENTILE.EXC(C17:C26, 0.5)

• C17:C26: Refers to the range containing the sales data for all 10 sales representatives.
• 0.5: Represents the 50th percentile or the median value in the dataset, excluding the boundary values.

In cell E17, the formula will return the value of 43900, which is the team's median (50th percentile) sales value, excluding boundary values.

Finding the 75th Percentile (Third Quartile) with PERCENTILE.INC and PERCENTILE.EXC

The 75th percentile, also known as the third quartile, represents the point below which 75% of the data falls. Using PERCENTILE.INC includes boundary values, while PERCENTILE.EXC excludes them, providing slightly different results depending on the dataset's distribution.

Example of PERCENTILE.INC

Let’s say now you’re looking at quarterly sales and want to find the 75th percentile to see which reps are in the top-performing group.

Formula Explanation:

=PERCENTILE.INC(C3:C12, 0.75)

• C3:C12: Refers to the range containing the sales data for all 10 sales reps.
• 0.75: Represents the 75th percentile, indicating the value below which 75% of sales data.

In cell E3, the formula will return approximately 54400, which represents the sales amount at the 75th percentile. This allows you to focus on the top-performing reps who contribute significantly to the business.

Example of PERCENTILE.EXC

Using the same monthly sales volumes for top reps, we will calculate the 75th percentile with PERCENTILE.EXC to focus on high performers while excluding boundary values.

Formula Explanation:

=PERCENTILE.EXC(C18:C27, 0.75)

• C18:C27: Refers to the range containing monthly sales data for all 10 sales reps.
• 0.75: Represents the 75th percentile, or the point below which 75% of the sales data falls, excluding extreme values.

This formula will provide a refined 75th percentile value, focusing on products that are performing in the upper range, excluding outliers.

Finding the 90th Percentile (Identifying Outliers) with PERCENTILE.INC and PERCENTILE.EXC

The 90th percentile is a useful measure for identifying outliers, highlighting the top 10% of data points in a dataset. With PERCENTILE.INC, all values are included, while PERCENTILE.EXC excludes boundary values, offering a more refined perspective.

Example of PERCENTILE INC.

Using the same monthly sales data, let's calculate the 90th percentile to identify the top 10% of reps with outstanding performance, focusing on high achievers and outliers.

Formula Explanation:

=PERCENTILE.INC(C3:C12, 0.9)

• C3:C12: Refers to the range containing the quarterly revenue data for all 10 reps.
• 0.9: Represents the 90th percentile, or the point below which 90% of the sales reps’ revenues fall, highlighting the highest-performing reps in the top 10%.

In cell E3, the formula will return approximately 58270, representing the revenue value at the 90th percentile.

Example of PERCENTILE.EXC.

Drawing from the monthly sales figures, let's calculate the 90th percentile to focus on top performers without including boundary values.

Formula Explanation:

=PERCENTILE.EXC(C16:C25, 0.9)

• C16:C25: Refers to the range containing monthly sales data for all 10 sales reps.
• 0.9: Specifies the 90th percentile, or the point below which 90% of the sales data lies, excluding extreme values.

This formula will help you return approximately 60430, representing the sales value at the 90th percentile, excluding boundary values.

Combining the PERCENTILE Function with Other Functions in Google Sheets

Combining the PERCENTILE function with other functions in Google Sheets opens the door to advanced data analysis. Integrating it with tools like IF or COUNTIFS allows you to categorize and compare data more efficiently. For even deeper insights, using PERCENTILE with the FILTER function helps refine datasets and identify key data points within specific conditions.

Using FILTER with PERCENTILE and QUARTILE

The FILTER function in Google Sheets allows you to apply conditions to your data. Combined with the PERCENTILE and QUARTILE functions, it becomes a powerful tool for analyzing specific segments. 

Example of FILTER with PERCENTILE

Suppose you are a retail manager analyzing the sales figures of sales reps, and you want to focus on reps that are performing in the bottom 70% of total sales. 

Formula Explanation:

=FILTER(B2:C, C2:C<=PERCENTILE(C2:C, 0.7))

• B2: Refers to the range containing both the names of sales reps (column B) and their sales amounts (column C).
• C2 <=PERCENTILE(C2 , 0.7): Filters the rows where sales amounts (in column C) are less than or equal to the 70th percentile of the dataset.

This output identifies the sales reps with monthly sales figures in the bottom 70% of performance, helping you focus on those who may need additional support or resources to improve their sales.

Example of FILTER with QUARTILE

Using the same monthly sales data, let's apply FILTER with QUARTILE to identify reps with sales above $40,000, then calculate the 3rd quartile to highlight the top 25% of high performers.

Formula Explanation:

=QUARTILE(FILTER(C3:C, C3:C>=40000), 3)

• C3: Refers to the range containing the sales data.
• FILTER(C3:C>=40000):  Filters the data to include only sales amounts greater than or equal to $40,000. This excludes any sales figures below this threshold.
• 3: Specifies the 3rd quartile, representing the value below which 75% of the filtered sales data falls, identifying the top 25% of high-performing reps.

Using the formula, the 3rd quartile value will be $50450. This means that any sales rep with monthly sales above or equal to $50450 is in the top 25% of high performers among those who achieved $40,000 or more.

‍

💡After exploring how to use the FILTER function with PERCENTILE and QUARTILE to analyze your data more effectively, take your data filtering skills to the next level. Learn more about mastering the Google Sheets FILTER function in this detailed guide from OWOX.

Resolving Common Issues with the PERCENTILE Function in Google Sheets

When using the PERCENTILE function in Google Sheets, you may encounter common issues such as errors or unexpected results. Identifying the cause of these problems – like incorrect input ranges or percentile values – can help you troubleshoot effectively. Understanding how to resolve these issues ensures accurate calculations and a smoother data analysis process.

#VALUE!

⚠️ Error: The #VALUE! error occurs in PERCENTILE.INC or PERCENTILE.EXC when non-numeric or invalid arguments are used. This can happen if the dataset contains text, empty cells, or if the percentile value is outside the 0–1 range.

✅ Solution: Ensure that your data range contains only numeric values. Remove any text or blank cells from the dataset. Verify that the percentile argument is a number between 0 and 1. If necessary, clean the data or convert non-numeric values before applying the function.

#NUM! Error

⚠️ Error: The #NUM! error occurs in PERCENTILE.EXC when the percentile value (k) is outside the valid range of 0 to 1. This error may also appear if the dataset contains fewer data points than required to calculate the specified percentile.

‍

✅ Solution: Ensure the percentile value (k) is between 0 and 1. Check your dataset to confirm it has enough numeric values for the calculation. Adjust the formula or clean the data to eliminate invalid values and avoid errors.

#N/A Error

⚠️ Error: The #N/A error in PERCENTILE.INC occurs when the dataset is empty, contains non-numeric values (like text), or the percentile value you’re calculating falls outside the dataset’s range.

✅ Solution: Ensure the dataset contains valid numeric values and is not empty. Remove any text or non-numeric entries. Verify that the percentile value is within the dataset’s range and adjust your formula if necessary.

#DIV/0! Error

⚠️ Error: The #DIV/0! error in PERCENTILE.EXC and PERCENTILE.INC occurs when the dataset is empty, contains no numeric values, or when the specified percentile value is outside the valid range (less than 0 or greater than 1).

✅ Solution: Ensure the dataset contains valid numeric values and is not empty. For percentile functions, verify that the percentile value (k) is between 0 and 1. Clean your data by removing non-numeric entries and double-check the formula parameters to avoid errors.

Best Practices for Effectively Using the PERCENTILE Function in Google Sheets

Using the PERCENTILE function in Google Sheets helps ensure accurate data analysis and valuable insights. By following key best practices, you can minimize errors, maintain data accuracy, and make more informed decisions. Organizing your data consistently and validating inputs will improve your results and enhance the reliability of your analysis.

Ensure Data Accuracy and Organization

Ensure your data is accurate and well-organized when using the PERCENTILE function in Google Sheets. Remove non-numeric values, verify that your dataset is complete, and ensure the percentile value is within the valid range for reliable results.

Understand the Context of Your Data

Understanding the context of your data is crucial when using the PERCENTILE function in Google Sheets. Analyze the dataset’s purpose and distribution before applying the function. Knowing whether you’re working with complete data or a sample ensures more accurate interpretations and helps identify meaningful trends or outliers for better decision-making.

Use Conditional Formatting for Better Visualization

Use conditional formatting with the PERCENTILE function in Google Sheets to highlight key data points visually. Apply color scales or custom rules to identify top-performing values, outliers, or specific percentiles at a glance. This approach improves clarity and helps you spot trends or anomalies quickly for more informed decisions.

Experiment with Different Data Ranges

Experimenting with different data ranges when using the PERCENTILE function can provide deeper insights. Analyze subsets of your dataset to focus on specific periods, categories, or conditions. This helps identify trends and patterns that may not be visible when working with the full dataset, offering more targeted and accurate analysis.

Use the Significance Parameter for Precise Results

Utilize the significance parameter in the PERCENTILE function to achieve more precise results. Adjusting the percentile value allows you to focus on specific data thresholds, such as the top 10% or bottom 25%. This approach helps refine your analysis and provides actionable insights tailored to your dataset’s needs.

Verify Your Results with Cross-Checks

Verifying your results using alternative methods or visual tools like graphs and charts is important. Cross-checking your calculations helps ensure accuracy and reliability in your analysis, allowing you to spot inconsistencies or errors before making decisions based on your data.

Powerful Google Sheets Functions for Enhanced Data Analysis

Learn the full power of Google Sheets, with essential functions tailored for comprehensive data analysis. These advanced formulas simplify complex tasks, enabling you to handle large datasets, automate processes, and extract valuable insights effortlessly.

• SUM, SUMIF, and SUMIFS: Adds values from a range, with SUMIF and SUMIFS allowing conditional summing based on one or multiple criteria, simplifying targeted calculations.
• UNIQUE: Removes duplicates, providing a list of distinct values for cleaner analysis and identifying unique data points.
• PIVOT: Automatically summarizes data with pivot tables, helping you report, organize, and visualize trends effortlessly.
• IMPORTRANGE: Imports data from external Google Sheets, consolidating multiple sources into one, streamlining your data analysis.
• MATCH: Finds the position of a value within a range, useful for dynamic lookups when combined with other functions like INDEX.
• COUNTA: Counts non-empty cells in a range, giving a quick overview of your dataset’s size and density.
• MAX, MIN, MEDIAN: Returns the highest, lowest, and middle values in a dataset, providing insights into data distribution and central tendency.
• AVERAGE: Calculates the mean of a set of numbers, highlighting central trends and performance averages in your data.

Effortlessly Analyze Your Data with OWOX: Reports, Charts, and Pivot Tables

OWOX simplifies data analysis by turning complex information into clear insights. Its advanced reports, charts, and pivot tables help you visualize trends, uncover patterns, and make informed decisions. You can easily manage large datasets and get the answers you need faster.

Through customizable reports and filters, OWOX allows you to focus on your business's most important metrics. Its user-friendly interface ensures you can work efficiently, saving time while improving the accuracy of your data analysis.