The terms percentiles and quartiles in Excel with related term percentile rank are often used in the reporting of scores from norm-referenced tests. It indicates the value below which a given percentage of observations in a group of observations fall.
While the term quartile in statistics refers to a division of observations into four quadrants.
Requirements and availability
Our readers should have a basic understanding of statistics to get the most out of this course and data entry forms are supported in Excel 365, Excel 2019, Excel 2016, Excel 2010, Excel 2007 and Excel Online (Excel for the web).
Here is how Percentiles and Quartiles in the Excel function works.
What are Percentiles?
The word percentile (centile) in statistics, refers to a score below which a given percentage of scores in its frequency distribution falls or a score below which a given percentage falls. The Excel PERCENTILE function calculates the “kth percentile” for a set of data.
For example, what is the 45th percentile of a list of numbers?
The term 45th percentile from the list indicates the number at which 45% of the list values fall at or below.
Percentiles are commonly used in international test score reports like the SAT, GRE and LSAT; professional exams like CFA, ACCA and PMP; and in quality assurance quality control (QA/QC).
What is Percentile Rank?
Percentile rank refers to the percentage of values that are equal to or less than a given value (<=x) in a list.
Percentile ranks are useful when you want to quickly understand how a particular value compares to the other values in a list.
Let consider some examples below, give a list of exam scores:
96, 77, 87, 57, 74, 59, 63, 75, 52, 54, 85, 85, 61, 95, 77.
From the above list, we would attempt to get the 25th percentile. Firstly, to get the percentile from the list you would need to sort the list in “ascending order” before adding a rank to the list items.
| EXAM SCORE | RANK |
| 52 | 1 |
| 54 | 2 |
| 57 | 3 |
| 59 | 4 |
| 61 | 5 |
| 63 | 6 |
| 74 | 7 |
| 75 | 8 |
| 77 | 9 |
| 77 | 10 |
| 85 | 11 |
| 85 | 12 |
| 87 | 13 |
| 95 | 14 |
| 96 | 15 |
Formula for calculating percentile:
Lp = (n + 1)*p/100
The formula above help give the exact percentile rank of your list data
L25 = (15 + 1)*25/100
L25 = 4.
This indicates that the rank is 4, which means the percentile value is valued at rank 4: 59.
Another example is getting the 45th percentile.
L45 = (15+1)*45/100
L45 = 7.2.
This indicates that the rank score is 7.2. Note that 7.2 is not in the list value so an approximation would be required
P45 = 74 + 0.20(1)
P45 = 74.2.
The 45th percentile is 74.2.
Now let’s repeat the same process of Percentiles and Quartiles in Excel
- The percentile formula needs to be filled with the list array i.e = PERCENTILE.INC(array,k).
- From your formula bar after typing out the Percentile formula you highlight the entire list of the exam scores.
- K is the percentile value we are looking for. We need to find the 25th percentile so we use “0.25” as the percentile value. (K is usually expressed in decimal, i.e 0.25 for 25%).
- After entering both arguments. =PERCENTILE.INC(A2:A16,.25), Excel would return the result as 60.
As you can see the result gotten from Excel is similar to the percentile result calculated.
Example 2: Get the 45th Percentile of Exam Score.
The same process is followed as the 25th percentile but this time around the K value is changed to 0.45.
K is the percentile value we are looking for. We need to find the 45th percentile so we use “0.45” as the percentile value. (K is usually expressed in decimal, i.e 0.45 for 45%).
After entering both arguments. =PERCENTILE.INC(A2:A16,.45), Excel would return the result as 74.3.
What is Quartile in Excel:
The quartile is denoted by 4 equal portions from the same list of data. The quartile has similar features to that of percentile.
Excel computes four quartiles which are:
- First Quartile: The first quartile in Excel is also the same as the 25th Percentile in Excel.
- Second Quartile: The second quartile in Excel is also the same as the 50th Percentile in Excel.
- Third Quartile: The third quartile in Excel is also the same as the 75th Percentile in Excel.
- Maximum Value: The maximum value in Excel is also the same as the 100th Percentile in Excel.
The QUARTILE function syntax has the following argument:
- Array: The array or cell range of numeric values for which you want the quartile value.
- Quart: Required. Indicates which value to return.
The QUARTILE function accepts 5 values for the quart argument, as shown in the table below.
| QUART VALUE | QUARTILE RETURN |
| 0 | Minimum Value |
| 1 | First Quartile(25th Percentile) |
| 2 | Second Quartile(50th Percentile) |
| 3 | Third Quartile(75th Percentile) |
| 4 | Maximum Value |
The figure above shows the similarity between Quartile and Percentile.
Examples of using Quartile in Excel:
Given the data from below, we are expected to calculate all the Quartile values for the data below.
Here, we are going to calculate the minimum value, First Quartile, Second Quartile, Third Quartile and Maximum value by using the Quartile Function.
Now we apply the Quartile Function to the dataset.
After entering both arguments. =QUARTILE.INC(A2:A16,0), this is used to get the minimum quartile value. Excel would return the result as 52. The value 52 from the Exam score indicates the minimum value.
The image above gives a clear illustration of how the Quartile function works in Excel. The Q0 is the minimum Quartile which indicates the lowest score, while the Maximum Quartile Q4 indicates the highest score value.
Other Relating topics:
- Create Data Entry Form in Excel without using VBA or Coding
Create Data Entry Form in Excel without using VBA or Coding – UrBizEdge
References:
Pingback: Custom Shape Map in Power BI - UrBizEdge