In this article:

Percentile in Google Sheets: The Ultimate Guide in 2024

percentile google sheets
source: spreadsheetpoint.com

What is Percentile in Google Sheets?

The percentile in Google Sheets is a statistical function that determines the value below which a specific percentage of data in a dataset falls. The percentile function is used to understand the distribution of data in a dataset.

Example: For instance, if you have a list of test scores and you want to find the score below which 30% of the scores fall, you would use the percentile function to calculate this. 

This function is particularly useful in data analysis for understanding how data is spread out and for identifying outliers or specific data trends.

Percentile Function in Google Sheets

The basic syntax for the percentile function in Google Sheets is

PERCENTILE(data, percentile), 

where:

  • Data: This is the range or array of data for which you want to calculate the percentile.

  • Percentile: This is the percentile you wish to calculate, expressed as a number between 0 and 1. For example, if you want to find the 30th percentile, you would use 0.3.

This function will return the value at the given percentile within your data set. For example, PERCENTILE(A1:A100, 0.3) will calculate the 30th percentile of the values in the range A1 to A100.

How to Use the Percentile Formula in Google Sheets

Follow the simple steps below to use the percentile formula in Google Sheets.

1. Open Google Sheets and Enter Data

Open Google Sheets and create a new spreadsheet. In column A, input names or identifiers, and in column B, enter the corresponding data values.

google sheets percentile

2. Select a Cell, Type and Execute the Percentile Formula

Choose an empty cell for the percentile result. Type the formula =PERCENTILE(B:B, followed by the desired percentile as a decimal, then close the formula and press Enter. For example, click on cell C2, type ‘=PERCENTILE(B:B, 0.3)’ to calculate the 30th percentile of scores in column B, and press Enter.

percentile formula google sheets

3. Interpret the Result

The selected cell will now display the calculated percentile value. In our example, cell C2 shows the score below which 30% of the students' scores fall.

percentile function google sheets

4. Adjust Percentile for Different Analysis

Modify the percentile number in the formula to explore various aspects of your data.

percentile in google sheets

You can change the formula in C3 to =PERCENTILE(B:B, 0.5) to find the median (50th percentile).

5. Apply to Various Data Sets

Use the same steps for percentile calculations on any other relevant data set in your spreadsheet.

We hope that you now have a better understanding of what a percentile in Google Sheets is how to use a percentile in Google Sheets. If you enjoyed this article, you might also like our article on how to set up the percent formula in Google Sheets or our article on how to do a t-test in Google Sheets.

Automate everything you track in spreadsheets with Lido
Learn more

Automate manual tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started