In this tutorial, you will learn how to use these functions to calculate percentiles and quartiles for your data sets in Excel. These two functions provide analysis on a certain percentage of your data that falls below or the value that separates a dataset into four equal parts, respectively.
Percentile
The PERCENTILE function in Excel is a statistical function that returns the value of a specified percentile in a set of values. A percentile is a value that separates a dataset into 100 equal parts, with each part representing 1% of the data. The function allows you to determine where a specified percentage of your data falls.
In this example, we have a list of students with their marks, and we want to know what value separates the bottom 20%. To do this, we will use the PERCENTILE function. Here’s a step-by-step guide:
- Enter the PERCENTILE function: Type “=PERCENTILE(“ in a cell.
- Select the data range: Select the range of cells that contain the data you would like to analyze. You can select the range by clicking and dragging over the cells, or by typing the cell addresses separated by a colon.
- Enter the percentile value: Enter the percentile value for which you want to find the corresponding value. This value must be between 0 and 1, and it represents the percentage of the data you would like to analyze. In this case, we want to find the 20th percentile, enter 0.2.
- Close the formula: Close the formula by typing a closing parenthesis, and press Enter.
The result should give a number that represents the value that separates the specified percentile of your data from the rest. In other words, 20% of the students had a score of 55.6 or lower.
Quartile
The QUARTILE function in Excel is a statistical function that returns the value that separates a dataset into four equal parts, known as quartiles. The function allows you to determine the values that divide your data into quarters, with each quartile representing 25% of the data.
Using the same example, we want to find the value that separates the first quarter from the rest. To do this, we will use the QUARTILE function. Here’s a step-by-step guide:
- Enter the QUARTILE function: Type “=QUARTILE(“ in a cell.
- Select the data range: Select the range of cells that contain the data you would like to analyze. You can select the range by clicking and dragging over the cells, or by typing the cell addresses separated by a comma.
- Enter the quartile value: Enter the quartile number you want to find. To find Q1, enter 1; to find Q2, enter 2; and to find Q3, enter 3. In this case, we want to find the first quarter, so we enter 1
- Close the formula: Close the formula by typing a closing parenthesis, and press Enter.
The result should give a number that represents the value that separates the specified quarter of your data from the rest. In other words, 25% of the students had a score of 58 or lower.
