Table of Contents
To find the top 10% of values in Google Sheets, one can use the built-in function “PERCENTILE” which calculates the specified percentile for a given data set. First, select the range of values from which the top 10% needs to be determined. Then, use the formula =PERCENTILE(range, 0.9) to find the value that is greater than 90% of the values in the selected range. This will give the threshold value for the top 10%. Next, use the “FILTER” function to filter the original range of values based on this threshold value. This will give the top 10% of values in a separate column. Finally, sort this column in descending order to get the top 10% of values at the top of the list. This method can be used to easily identify and analyze the highest values in a data set, providing valuable insights for decision making.
Find the Top 10% of Values in Google Sheets
The following step-by-step example shows how to highlight the top 10% of values in a column in Google Sheets.
Step 1: Enter the Data
First, let’s enter the following dataset into Google Sheets:
Step 2: Highlight Top 10% of Values
Next, click the Format tab and then click Conditional formatting:
In the Conditional format rules panel that appears on the right side of the screen, type B2:B21 in the Apply to range box, then choose Custom formula is in the Format rules dropdown box, then type in the following formula:
=PERCENTRANK($B$2:$B$21,B2)>=90%
Once you click Done, the top 10% of values in the Points column will be highlighted:
If you would like to highlight the entire row that contains the top 10% of values in the Points column, type A2:B21 in the Apply to range box, then choose Custom formula is in the Format rules dropdown box, then type in the following formula:
=PERCENTRANK($B$2:$B$21,$B2)>=90%Once you click Done, the entire row that contains the top 10% of values in the Points column will be highlighted:
Additional Resources
Cite this article
stats writer (2024). How can I find the top 10% of values in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-find-the-top-10-of-values-in-google-sheets/
stats writer. "How can I find the top 10% of values in Google Sheets?." PSYCHOLOGICAL SCALES, 30 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-find-the-top-10-of-values-in-google-sheets/.
stats writer. "How can I find the top 10% of values in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-find-the-top-10-of-values-in-google-sheets/.
stats writer (2024) 'How can I find the top 10% of values in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-find-the-top-10-of-values-in-google-sheets/.
[1] stats writer, "How can I find the top 10% of values in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I find the top 10% of values in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.