Table of Contents
The PERCENTILE.EXC function in Google Sheets is a powerful tool that allows users to calculate the value at a specified percentile in a given dataset. This function excludes the value at the specified percentile, making it useful for data sets with a large number of values. By simply inputting the range of data and the desired percentile, users can quickly and accurately determine the value at that percentile. This function is particularly helpful for analyzing data sets with a large number of data points and can assist in making informed decisions based on the distribution of the data. Overall, the PERCENTILE.EXC function in Google Sheets is an efficient and reliable method for calculating percentiles in a dataset.
PERCENTILE.EXC function
The PERCENTILE.EXC function returns the value at a given percentile of a dataset, exclusive of 0 and 1.
Parts of a PERCENTILE.EXC function
PERCENTILE.EXC(data, percentile)
| Part | Description |
data | The array or range containing the dataset to consider. |
percentile | The percentile whose value within data will be calculated and returned. |
Sample formulas
PERCENTILE.EXC(A2:A100,0.33)
PERCENTILE.EXC(A2:A100,A2)
Notes
- The value returned by
PERCENTILE.EXCis not necessarily a member of data as this function interpolates between values to calculate the alpha percentile. - The percentile must be between 0 and 1, exclusive.
- Different from
PERCENTILE.INC,PERCENTILE.EXCcalculates percentile based on a percentile range exclusive of 0 and 1.
Examples
| A | B | |
| 1 | data | percentile |
| 2 | 15 | 0.15 |
| 3 | 20 | 0.4 |
| 4 | 35 | 0.5 |
| 5 | 40 | 0.8 |
| 6 | 50 | 0.9 |
| A | B | C | D | |
| 1 | Percentile | Result | Formula | Explanation |
| 2 | 0.15 | #NUM! | =PERCENTILE.EXC(A2:A6, B2) | Returns #NUM! error because it can’t interpolate. |
| 3 | 0.4 | 26 | =PERCENTILE.EXC(A2:A6, B3) | Executes as intended. |
| 4 | 0.5 | 35 | =PERCENTILE.EXC(A2:A6, B4) | Executes as intended. |
| 5 | 0.8 | 48 | =PERCENTILE.EXC(A2:A6, B5) | Executes as intended. |
| 6 | 0.9 | #NUM! | =PERCENTILE.EXC(A2:A6, B6) | Returns #NUM! error because it can’t interpolate. |
Related functions
- PERCENTILE: Returns the value at a given percentile of a dataset.
- QUARTILE: Returns a value nearest to a specified quartile of a dataset.
- MEDIAN: Returns the median value in a numeric dataset.
- QUARTILE.EXC: The QUARTILE.EXC function returns value nearest to a given quartile of a dataset, exclusive of 0 and 4.
Cite this article
stats writer (2024). How can I use the PERCENTILE.EXC function in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-percentile-exc-function-in-google-sheets/
stats writer. "How can I use the PERCENTILE.EXC function in Google Sheets?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-percentile-exc-function-in-google-sheets/.
stats writer. "How can I use the PERCENTILE.EXC function in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-percentile-exc-function-in-google-sheets/.
stats writer (2024) 'How can I use the PERCENTILE.EXC function in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-percentile-exc-function-in-google-sheets/.
[1] stats writer, "How can I use the PERCENTILE.EXC function in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the PERCENTILE.EXC function in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.