PERCENTILES AND QUARTILES IN EXCEL

PERCENTILES AND QUARTILES IN EXCEL

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 and Quartiles in the Excel

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 SCORERANK
521
542
573
594
615
636
747
758
779
7710
8511
8512
8713
9514
9615
Percentile Rank Table

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

  1. The percentile formula needs to be filled with  the list array i.e = PERCENTILE.INC(array,k).
  2. From your formula bar after typing out the Percentile formula you highlight the entire list of the exam scores. 
  3. 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%).
Percentiles and Quartiles in the Excel
  1. 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 VALUEQUARTILE RETURN
0Minimum Value
1First Quartile(25th Percentile)
2Second Quartile(50th Percentile)
3Third Quartile(75th Percentile)
4Maximum 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.

Percentiles and Quartiles in the Excel

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.

Percentiles and Quartiles in the Excel

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.

Percentiles and Quartiles in the Excel
Quartiles Explanation

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:

Leave a Reply

Business Data Analysis
close slider
[]
1 Step 1

Interested in our Business Data Analysis Training?

keyboard_arrow_leftPrevious
Nextkeyboard_arrow_right