Associated Learning Objectives: 

  • Find and interpret percentiles and quartiles of a business-related data set
  • Find and interpret percentiles and quartiles of a data set
  • Find the five-number summary of a business-related data set
  • Find the five-number summary of a data set

Step-by-Step Instruction:

Calculate a Percentile From a Data Set:

  1. Paste the data into Column A in Google Sheets.
  2. Select all of the cells with data values, click the Data menu option, and select Sort range by column A, A→Z to arrange the data in order from least to greatest. (If the data are already arranged from least to greatest, this step can be skipped.) The minimum value of the data set is the first value, and the maximum value of the data set is the last value.
  3. In a blank cell, enter “=PERCENTILE(A1:A{Row},{Dec})”, where {Row} is the row of the last value in the data set and {Dec} is the decimal form of the desired percentile. For example, to find the 90th percentile of a data set from cell A1 to cell A50, enter “=PERCENTILE(A1:A50,0.9)” in a blank cell. The resulting value will be the desired percentile.

 

Calculate a Percentile From a Frequency Distribution:

  1. Paste the frequency distribution into cell A1 of Google Sheets so the values are in column A and the frequencies are in column B. The minimum value of the data set is the smallest value in column A that has a frequency in column B that is greater than 0. The maximum value of the data set is the largest value in column A that has a frequency in column B that is greater than 0.
  2. Enter 1 into cell C2 and enter the formula "=INDEX($a$2:$a$#,C2)" into cell D2, where # is the row number of the final row of the frequency distribution. The formula in column D will pick values from the value list based on the values in the index column.
  3. Enter the formula "=IF(COUNTIF($D$2:D2,D2)=INDEX($B$2:$B$6,C2),C2+1,C2)" into cell C3. This formula determines the index of the next value. "COUNTIF($D$2:D2,D2)" counts the number of times the last value in column D occurs. “INDEX($B$2:$B$6,C2)” gives the number of times that value should occur. If the two are equal, then then index is increased by 1. Otherwise, more of that value is needed, so the index is not changed.
  4. Copy and paste the formula from D2 into cell D3.
  5. Select cells C3 and D3 and use the fill down feature to copy the formulas until all the values are listed in column D.
  6. In a blank cell, enter “=PERCENTILE(D2:D{Row},{Dec})”, where {Row} is the row of the last value in the data set and {Dec} is the decimal form of the desired percentile. For example, to find the 90th percentile of a data set from cell D2 to cell D50, enter “=PERCENTILE(D2:D50,0.9)” in a blank cell. The resulting value will be the desired percentile.

Did this answer your question?