Google Sheets

PERCENTILE: Google Sheets Formulae Explained

How do you use PERCENTILE in Google Sheets?

There are a few ways to use percentile in Google Sheets. You can find the percentile of a value in a column, or the percentile of a row. You can also find the percentile of a range of cells.

To find the percentile of a value in a column, use the PERCENTILE function. The syntax for the function is:

=PERCENTILE(array, k)

Where "array" is the range of cells that you want to calculate the percentile for, and "k" is the position of the value you want to find the percentile for in the "array".

For example, if you wanted to find the percentile for the value "10" in the column "A", you would use the following formula:

=PERCENTILE(A:A, 10)

This would return the value "80". This means that 80% of the values in the "A" column are less than or equal to 10.

To find the percentile of a row, use the PERCENTILE.INC function. The syntax for the function is:

=PERCENTILE.INC(array, k)

Where "array" is the range of cells that you want to calculate the percentile for, and "k" is the position of the value you want to find the percentile for in the "array".

For example, if you wanted to find the percentile for the value "10" in the row "B", you would use the following formula:

=PERCENTILE.INC(B:B, 10)

This would return the value "90". This means that 90% of the values in the "B" column are less than or equal to 10.

To find the percentile of a range of cells, use the PERCENTILE.RANGE function. The syntax for the function is:

=PERCENTILE.RANGE(array1, array2, k)

Where "array1" is the lower bound of the range of cells that you want to calculate the percentile for, "array2" is the upper bound of the range of cells that you want to calculate the percentile for, and "k" is the position of the value you want to find the percentile for in the "array1" and "array2".

For example, if you wanted to find the percentile for the value "10" in the range of cells "A1:B10", you would use the following formula:

=PERCENTILE.RANGE(A1:B10, A10:B10, 10)

This would return the value "80". This means that 80% of the values in the range of cells "A1:B10" are less than or equal to 10.

What is the syntax of PERCENTILE in Google Sheets?

The syntax of PERCENTILE in Google Sheets is as follows:

=PERCENTILE(array,percent)

Where:

array is the array of numbers you want to calculate the percentile of

percent is the percentile you want to calculate

What is an example of how to use PERCENTILE in Google Sheets?

The PERCENTILE function in Google Sheets calculates the percentage rank of a value in a given set of data. The function takes in an input range of cells and a number indicating which value to find the rank of. It then outputs the percentage rank of that value in the input range. For example, if you wanted to find the rank of the value "5" in the range of cells A1:A10, the PERCENTILE function would return the value "5" and output the percentage rank of "50%" because "5" is the 5th value in the input range.

When should you not use PERCENTILE in Google Sheets?

There are a few occasions when you should not use the PERCENTILE function in Google Sheets. First, if you are looking to find the rank of a value within a data set, you should use the RANK function instead. Second, if you are working with a data set that has a limited number of data points, the PERCENTILE function may not return accurate results. Finally, if you are trying to find the percentile rank of a value that is not in your data set, the PERCENTILE function will not work.

What are some similar formulae to PERCENTILE in Google Sheets?

In Google Sheets, the PERCENTILE() function returns the kth percentile of a given array. The function takes two arguments: the array to be sorted and the position of the desired percentile.

The percentile is the value in the array at the position given by the second argument, k, such that the percentage of the values in the array less than or equal to the percentile is k/100.

The following are some similar formulae to PERCENTILE() in Google Sheets:

PERCENTILE.INC() - Returns the percentile of a given array, increased by 1.

RANK() - Returns the rank of a given value in a given array, from smallest to largest.

QUARTILE() - Returns the quartile of a given array, based on the given percentile.

Move beyond 

Google Sheets

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.