How to Calculate Quartile Deviation in Excel

Quartile Deviation is a very important thing in the world of data analysis and statistics. Data analysts, financial researchers, and statisticians are using this all day. In every sector, there is some little or huge effect of this. Yes! people with statistics or mathematics backgrounds know very well about this but the other guys may suffer to understand the topic. So, in this article, I have tried to explain and show how to calculate quartile deviation in Excel with various methods. I have added some examples so that it becomes very easy to understand for the readers. You can also try these on your own and then check the answer with the article. You can download the free workbook for practice. In this article, you will find quartile calculation methods for grouped and ungrouped data. So, let’s dig into the main part.

📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

Overview of Quartile Deviation in Statistics

Quartile Deviation is a measure of variation or distribution in a set of data. It is also known Interquartile Range (IQR). Quartiles divide the set of observations into four equal parts. The first quartile. which is labeled as Q1, indicates the value which is higher than the 25% value of the dataset. And the third quartile (Q3) indicates a value that is higher than 75% values of the dataset.

The Quartile Deviation (IQR) is defined as the difference between the upper and lower quartiles and represents the range of values that contains the middle 50% of the data set. In brief, IQR provides information about the spread of the middle 50% of the data, excluding the lowest and highest 25% of observations, which may contain outliers. The IQR is an essential tool in data analysis to identify outliers, skewness and to create Box Plot of a dataset.

Formula of Quartile Deviation

When calculating quartile deviation, we have to deal with two data types. 1. Ungrouped Data and 2. Grouped Data. Here, I am showing formulas for two types in separate sections.

Formula of Quartile Deviation for Ungrouped Data

To calculate the quartile deviation for ungrouped data, first, we have to calculate the 1st quartile (Q1) and 3rd quartile (Q3). Then, from them, we can calculate the QD and coefficient of QD.

Here are the formulas for quartile deviation calculation for ungrouped data. Assume, n is the total number of observations of the dataset.

1st Quartile (Q1)

3rd Quartile (Q3)

Quartile Deviation (QD)

Coefficient of Quartile Deviation

Formula of Quartile Deviation for Grouped Data

Here, first, we have to calculate the 1st quartile (Q1), and 3rd Quartile (Q3). For grouped data, you have to use these formulas for calculating quartile deviation.

So, the formula for r’th quartile will be:

1st Quartile (Q1)

3rd Quartile (Q3)

The formula for quartile deviation and coefficient of quartile deviation is the same for both grouped and ungrouped datasets.

Learn to Calculate Quartile Deviation for Ungrouped Data in Excel with These Methods

Here we have a dataset of scores of 3 subjects in the final exam of a college. So, it is necessary to know the distribution of the data, so we can understand the quality of teaching in that college. There are several ways to calculate quartile deviation in Excel. We can use direct formulas by manually inserting values from the dataset and formulas. Also, we can automate the process by using Excel functions and VBA. Let’s go through the main part of the procedures.

At first, I am showing the easiest and most handy method to calculate the quartile deviation for ungrouped data in Excel. Here, I will use QUARTILE.INC and QUARTILE.EXC functions for this.

Overview of QUARTILE Function

It is a very useful function for getting the quartile value in Excel. The syntax of Quartile function is as follows:

Here, Array is the cell range from which to calculate the quartile and quart is the number of quartiles. For quartile 1 insert 1, and for quartile 3 insert 3 as the quart value.

Now, let’s see the steps of how you use these functions to calculate the quartile deviation value.

⬇️⬇️ STEPS ⬇️⬇️

Here, we have the percentage value in cell range G5:G24. For these values, I want to calculate the quartiles.

• First, I will apply INC function to get quartiles including the median in cell range. So, I have inserted the following formula in cells J6 and J7 to get the Q1 and Q3 respectively.

For Q1,

For Q3,

Here, I have used quart values as 1 for Q1 and 3 for Q3. And G5:G24 is the cell range.

• As a result, we have got Q1 as 62.58% and Q2 as 74.58%.
• Now, for calculating the Quartile Deviation, QD, insert the following formula in cell J9.

Here, actually, we have used the formula “QD = (Q3 – Q1) / 2”. So, we have got =(0.745-0.625)/2 = 0.0600.

• And, for calculating the coefficient of QD, insert the following formula in cell J10:

Here, we have used the formula “(Q3 – Q1) / (Q3 + Q1)” for calculating the coefficient of QD. As a result, we got 0.0875.

• Similarly, we can use EXC function. Then the result will be slightly different. Here, we have got Q1 as 62.42 and Q3 as 75.08. And the QD value is 0.0633 for this case.

In the first section, we have shown the formula for quartile calculation. Now, we will use them for calculating the values. This is a manual process, so with this procedure first you have to calculate the position of quartile 1 and quartile 3 and then, calculate the value of quartile deviation. We can see this image, so you will get an overall idea about the process. Also, for better understanding, I am explaining the full process step by step.

📌 Step 1: Get Position of Quartile 1 and Quartile 3

First, you have to get the position of the quartiles then you can calculate their value. So, select two cells where you want to put the values of quartile 1 and quartile 3. Here, I have assigned cells K6 and K9. Insert the following formula in cell K6 to get the position value of quartile 1 in the dataset.

Also, insert the following formula in cell K9 to get the position value of quartile 3:

As a result, we have got the position of Q1 = 5.25 and Q3 = 15.75. That means the Q1 value is the value of the 5.25th observation and the Q3 value is the 15.75th observation.

📌 Step 2: Calculate Value of Quartile 1 and Quartile 3

After getting the position value, we can calculate their value by manually inserting the parameters from the dataset. As Q1 is at the position of 5.25th so, we have to insert the 5th and 6th observation values manually in the formula.

• Insert the following formula in cell K7 to calculate the quartile 1 value:

Here, H9, and H10 are the values of the 5th and 6th observations consecutively. It is actually an interpolation formula. We are calculating the 5.25th observation value from the value of the 5th and 6th observations.

• Similarly, insert the following formula in cell K9 for calculating the quartile 3 value:

Also, here H19 and H20 are the observation values of 15th and 16th consecutively.

📌 Step 3: Calculate Quartile Deviation and Coefficient

Now we have quartile 1 and quartile 3 values, so we can easily calculate the quartile deviation and coefficient from them.

• For getting the QD value, insert the following formula in cell K12:

Here, K10 and K7 are the values of Q1 and Q3 consecutively.

• Finally, if I wish to get the coefficient of QD value in cell K14, I have to use the given formula:

In the previous method, I inserted cell references manually for getting the quartile values. So, I have created a dynamic method by which you can get the quartile values with just one click only. I have added an image where you will get an idea of how this works. By using this you will get the quartile value and then with these, so you can easily calculate the quartile deviation and coefficient value.

Follow the below steps to create an automated quartile value calculator in Excel.

📌 Step 1: Get the Position of Quartiles

We are using the same dataset for these values. This is ungrouped data sorted by lowest to highest order. You can easily sort the dataset by using the Excel Sort feature or by converting it into an Excel table.  After making the dataset ready, you can calculate the quartile value in this automated way.

• First, calculate the position of the quartile. Insert the quartile number in cell C28 and insert the following formula in cell E28:

• So, we have got the position of quartile and for Q1 it is the 25th observation. Then, I have to insert observation numbers between which the quartile exists. So, I want to get 5 in cell C31 and 6 in cell E31. But to automate this, I have inserted the following formulas in cell C31 the immediate lower observation position.

• After that, I added 1 with the value of C31 to get the value of the immediate higher position of the quartile. For this, I have inserted this formula in cell E31:

📌 Step 2: Get the Value Immediate Higher and Lower Observation of Quartile

In the last step, we have calculated the position of the immediate higher and lower observations of the quartiles. Now, we will calculate their values using the INDEX function.

• To get the value of immediately before the observation of the quartile, insert the following formula in cell E34:

• Similar for getting the immediate next observation value of the quartile, insert this formula in cell E35:

 📌 Step 3: Calculate the Quartile Value

Now, we can easily get the Quartile value using the interpolation formula. Insert the following formula in cell E37 to get the Quartile Value.

 ⛏️ Formula Breakdown:

In this way, you can get any quartile value by just inserting the number of the quartile in cell E28. Here I am showing a gif image, so you can easily understand the matter.

📌 Final Step: Calculate Quartile Deviation and Coefficient

From the previous step, you will get the Q1 and Q3 value. Using them, you can calculate the QD and coefficient of QD.

• First, get the Q1 and Q3 value from the automated calculator and insert those values in cells J6 and J7
• Then, insert the following formula to calculate the QD in cell J9:

• Then, for calculating the coefficient of QD, insert the given formula in cell J11:

Thus, you can easily calculate the quartile value using an automated calculator. It will ease your work a  lot.

Alternatively, we can use the AGGREGATE function for calculating the quartile values. This function is a wholesome function of Excel that includes all basic calculations of statistics.

So now you have an idea of how you can use this function to calculate the quartile value of a dataset.

Using the AGGREGATE function, we can calculate both quartiles excluding and including the median. Just for the value of QUARTILE.INC, you have to use function number 17 and for the value of QUARTILE.EXC, you have to use function number 19. And for quartile 1, insert the k value as 1. Similarly, for quartile 3, insert the k value as 3. In the following image you will get an overview of the procedure.

⬇️⬇️ STEPS ⬇️⬇️

For better understanding, I am describing the steps one by one so you can easily trace the formula.

• To calculate the quartile 1 value including the median value, I have used the following formula in cell J5:

• Similarly to get the value of quartile 3, insert the following formula in cell J6:

Thus, we have got the Q1 and Q3 value as 62.58% and 74.58%. Similarly, I have calculated these values for the case of excluding the median of the dataset in cells J15 and J16. Just I had to change the function number from 17 to 19 for this case and all the remaining part is the same. Also, I have used a similar formula for quartile deviation calculation.

5. Using VBA Macro

To automate the process, we can use VBA macro code in Excel. If you are a code lover then you will get fun in this method. With this code, just you have to select the data range and the values of Q1, Q3, and QD will be automatically inserted in the dedicated cells. Let’s see the steps on how you can use the code to calculate quartile deviation in Excel.

⬇️⬇️ STEPS ⬇️⬇️

• For this, first, go to the top ribbon and press on the Developer, And then press on the Visual Basic option from the menu.

You can use ALT + F11 to open the “Microsoft Visual Basic for Applications” window if you don’t have the Developer tab added.

• Now, a window named “Microsoft Visual Basic for Applications” will appear. Here from the top menu bar, press on the “Insert” And a menu will appear. From them, select the “Module’” option.

• Now, a new “Module” window will appear. And Paste this VBA code into the box.

Sub QuartileDeviation()
Dim mean As Double
Dim median As Double
Dim Q1 As Double
Dim Q3 As Double
Dim IQR As Double
Dim n As Long
Dim i As Long
Dim j As Long
Dim data As Range
Set data = Application.InputBox(Prompt:="Select the range of data to calculate the quartile deviation for:", Type:=8)
n = data.Count
ReDim Values(1 To n)
For i = 1 To n
Values(i) = data(i).Value
Next i
mean = Application.WorksheetFunction.Average(data)
median = Application.WorksheetFunction.median(data)
Q1 = Application.WorksheetFunction.Percentile(data, 0.25)
Q3 = Application.WorksheetFunction.Percentile(data, 0.75)
IQR = Q3 - Q1
Range("K6").Value = Q1
Range("K7").Value = Q3
Range("K9").Value = IQR / 2
End Sub

• To run the code go to the top menu, press on the Run option, and here will open some other options select the Run Sub/UserForm also you can simply press F5 to run the code.
• After clicking you will see a pop-up window will appear named “Input”. You have to select the cell range for which you want to get the quartile value. Here, I have selected cell range G5:G25 and pressed the OK button.

• In a moment you will see that the designated cells for Q1, Q3, and QD are filled up with results.

Learn to Calculate Quartile Deviation for Grouped Data in Excel with These Methods

Grouped data means actually that dataset where the observations are listed between some class intervals. The class interval is usually the same for all the classes. This is also an important type that we face in our practical life. So, it is important to learn how you can calculate the QD value for grouped data in Excel.

Here, we have a dataset of 10 classes and each of 5 intervals. For each class, the number of observations that occurred is listed in the frequency column, and also cumulative frequency column is created which is very necessary for calculating the quartiles of grouped data

Now, we will calculate the quartile deviation for this grouped data with some distinct methods. Let’s see the big picture.

Here, in this method, I will use the basic formula to find the position of the quartiles then I will insert the required parameters manually from the dataset and calculate the value of quartiles. Let’s go deeper so you can get an idea of how I have calculated quartiles in a very simple way. First, see the following image, so you will get an overall idea about the process.

Now, I will describe the process with steps and formulas.

⬇️⬇️ STEPS ⬇️⬇️

• First, find the position of quartile 1 using the following formula in cell I6:

• Similarly for quartile 3, use this formula in cell J6:

We have used the formula “Qr= r*(n)/4” to get the position of the quartile. Here, r is the quartile number and n is the total number of observations.

• Now, find the class in which the quartile value contains. As we have got Q1 position = 25. so, Q1 will be in the next class of 61-65. Because, till this class, the cumulative frequency is 31. So, the quartile 1 class is 66-70. You have to find it manually and insert in cell I8 for Q1 and J8 for Q3.
• Then, insert the lower limit and higher limit of the quartile class in cells I9 and I10 respectively for Q1. Do the same thing for Q3
• Now, insert the number of observations which is the value of the last cell of the cumulative frequency column in cells I11 and J11.
• After that, insert the frequency of the quartile class in cell I12 for Q1 and J12 for Q3.
• Now, insert the last parameter for the quartile calculation of grouped data which is the cumulative frequency till the quartile class. For Q1, the last class before the quartile class is 61-65 and the cumulative frequency of this class is 31. So insert 31 in cell I13. Similarly, insert 79 in cell J13.

• Now, insert the following formula in cell I15 to calculate the value of Q1:

• Similarly, insert this formula in cell J15 for Q3 value:

Here, we have used this formula: “rth Quartile, Qn = h1 + (h2-h1)/ f *( r * (n/4) – c)”. Thus, we have got the Q1 and Q3 value for the grouped data.

• Now for calculating the QD value insert the following formula in cell J17:

Here, J15 and I15 cells contain the Q3 and Q1 value respectively.

• Finally, to get the coefficient of QD, insert the following formula in cell J19:

Here, we have used the formula: Coefficient of QD = (Q3 – Q1) / (Q3 + Q1). Thus, you can easily calculate the quartile deviation of grouped data.

In the previous method, you had to insert the parameter manually from the dataset to calculate the quartile value. But it’s a bit cumbersome work while dealing with large datasets. So, I have made an automated quartile value calculator for grouped data. Here, you just have to insert the number of quartiles in a cell and you will get the quartile value in the designated cell within a moment. Here, I am adding a gif image so you will get an idea how it looks like.

Now, I will show you how I have made it. So, you can easily modify it for your dataset. Let’s see the detailed steps.

⬇️⬇️ STEPS ⬇️⬇️

• First, get the number of observations (n) in cell F18 which is equal to the last cumulative frequency of the dataset. So, link the cell with the last cell of the cumulative frequency column or insert the value manually.
• Then, insert the quartile number in cell D20 and get the position of the quartile in a cell For this, you have to insert the following formula in cell F20:

Here, we have used the formula: Qr= r*(n+1)/4; Where, r= Quartile Number and n = Observations Count

• Then, insert the following formula in cell F22 to find the cumulative frequency of Quartile

Here, the IF function returns only the values of the range F5:F14 which are larger than the value of F20. And the MIN function returns the minimum value of them. For Q1, we got the cumulative frequency of the quartile class is 47.

• This step was necessary because using this value we will get the serial number of the quartile class. And for this, I will imply the MATCH function in cell F23:

Here, the MATCH function searches for the cell value of F22 in cell range F5:F14 and returns the row number that matches. Thus, we would get the serial number of the quartile class in the dataset. For Q1, we have got the serial number 4.

• After finding the serial number of quartile class, you can easily get other parameters of quartile value calculation using the INDEX So, to find the value of quartile class insert the following formula in cell F25:

Thus, we have got the quartile 1 class which is 66-70.

• Then, to find the lower limit of the quartile class (h1), insert the following formula in cell F26:

Thus, the INDEX function extracts the 4th value of cell range C5:C14 and this is the lower limit of quartile class = 65.5.

• After that, to find the higher limit of the quartile class (h2), insert the following formula in cell F27:

As a result, we have got the higher limit of the quartile class = 70.5.

• To find the frequency of the quartile class, insert the following formula in cell F28:

So, the frequency of the quartile 1 class is 16.

• Find the cumulative frequency till the quartile class by inserting this formula in cell F29:

As a result, we have got the value of cumulative frequency till the quartile 1 class = 31.

This is how you can easily make a quartile calculator. Just insert the number of quartile in the assigned cell and you will get the quartile value of grouped data in the designated cell. For our dataset, we have got the Q1 value as 65.58 and Q3 value as 89.38.

• Insert the value of Q1 in cell I6 and Q3 in cell I7.
• Then, insert the following formula in cell I9 to get the value of quartile deviation of the grouped data.

As a result, we have got the value of QD as 11.90 which is equal to the result of the previous method. Thus, you can calculate the quartile deviation value in an automated way. I believe you are an Excel enthusiast so if you face any regarding these methods, please inform us in the comment box.

Examples of Calculating Quartile Deviation in Excel

Now, I will show you some examples of calculating quartile deviation in Excel. So, it will be easier for you to understand the topic.

Suppose, you have sales data of your company for the previous 12 years. Now you want to calculate the quartile deviation of this data to know at which period, you had the best sales. So, let’s see how you can solve this.

Calculation of First Quartile (Q1)

We know, Qr = [r * (n+1)/4] th observation, So, Q1 = 1*(12 + 1) = 3.25th observation.

So, the Q1 value is between the 3rd and 4th observation.

=> Q1 = 3rd Observation + 0.25* ( 4th observation – 3rd Observation)

=> Q1 = 1178 + 0.25*(1362-1178)

=> Q1 = 1700.75

Calculation of First Quartile (Q1)

=> Q3 = 3*(12 + 1) = 9.75th observation.

So, the Q3 value is between the 9th and 10th observations.

=> Q3 = 9th Observation + 0.75* ( 10th observation – 9th Observation)

=> Q3 = 2883+0.75*(2220-2883)

=> Q3 = 2385.75

Calculation of Quartile Deviation (QD)

=> QD = (Q3-Q1)/2

=>  QD = (2385.75-1700.75)/2

=> QD = 342.5

This is the hand calculation result. I have also done the same thing in Excel and it is shown in the following image.

I want to show another example which is for grouped data. Suppose, you are studying the accident frequencies and age group relation and you have a related dataset. Now you want to calculate the two quartiles and the quartile deviation of the dataset.

So, this is a grouped data example or frequency distribution problem. For this case, the formula of Quartiles is:

Here,

• h2 = Lower Limit of Quartile Class
• h1 = Higher Limit of Quartile Class
• f = Frequency of Quartile Class
• n = Total Number of Observations
• c = Cumulative Frequency till the Quartile Class

So, first, you have to create the class boundary column, and cumulative frequency column. You can simply create these columns by seeing the following image.

So, here Number of observations (n) = 1431

Calculation of First Quartile (Q1)

Using formula,

=> Q1 = 1*(1431/4) = 357.75th Observation

From the dataset, we see 357.75th Observation is in 31-40 age group.

So, for this case, h2 = 40.5, h1 = 30.5, f = 175, n = 1431, c = 242.

=> Q1 = h1 + ((h2 -h1) / f) * ( 1 * (n/4) – c)

=> Q1 = 30.5 + ((40.5 – 30.5) / 175) * ( 1 * (1431/4) – 242)

=> Q1 = 37.1142

Calculation of First Quartile (Q3)

Using the formula,

=> Q3 = 3*(1431/4) = 1073.25 th Observation

From the dataset, we see 1073.25 th Observation is in 60-79 age group.

So, for this case, h2 = 70.5, h1 = 60.5, f = 271, n = 1431, c = 1010.

=> Q3= h1 + ((h2 -h1) / f) * ( 1 * (n/4) – c)

=> Q3= 60.5 + ((70.5 – 60.5) / 271) * ( 3 * (1431/4) – 1010)

=> Q3  = 62.8339

Calculation of Quartile Deviation (QD)

=> QD = (Q3-Q1)/2

=>  QD = (62.8339-37.1142)/2

=> QD = 12.86

These are the results of hand calculations. I have also done this in an Excel file. The following image is showing the results of the Excel calculation.

After solving these examples, I think all problems related to the quartile calculation will be solved.

Difference Between Quartile, Decile, and Percentile

In this article, we have discussed quartiles. So, what is a quartile? Quartile divides an organized distribution or dataset into four identical parts. Similarly, decile and percentile divide the organized distribution into some uniform parts. Here, the basic thing is, Decile divides the set of observations into ten equal parts. On the other hand, the percentile divides the set of observations into hundred equal parts.

Usage of Quartile Deviation in Data Analysis and Statistics

When you have a large dataset then you may need to keep a clear concept about your data range or the central tendency or where 50% of your data has been. In that case, quartile deviation is a very important thing.

Furthermore, you should deal with a real range of your dataset. The maximum value of your dataset can’t be a real one. So, here, the quartile deals with the first to third identical or equal part of your dataset which is the real range.

Again, a quartile with a box plot is used by some companies to benchmark the other ones.

Similarly, in some cases, where we ignore the first and last 25% of the data, we use this quartile deviation like weather forecasting.

Related Things of Quartile Deviation in Data Analysis

Apart from quartile deviation, there are a lot more important parameters that are also very useful in our daily life. I am giving short descriptions of some of the related parameters in the data analysis world.

1. Measures of central tendency:
   1. Mean: The average value of some specified numbers.
   2. Median: Denotes the middle number of a certain range which is organized in ascending or descending order. If the range contains an odd number of data then the Median will be one otherwise Median will be two.
   3. Mode: Defines that particular frequency which is occurred at the highest times. Suppose, you have frequencies like 2,3,5,5,6,8. Then the Mode will be 5. However, if you get more than one frequency occurred maximum times then all of them will be the Mode.
2. Measures of dispersion:
   1. Range: Denotes a dataset that maintains a similar type. This range is varied within a given limits.
   2. Variance:  Means how scatter the points of a data from their average value.
   3. standard deviation: It is similar thing to Variance. Basically, the square root of variance is the standard deviation.
3. Box plot: A graphical representation of quartile deviation.
4. Outliers: Data points that fall outside the range defined by the quartile deviation.
5. Five-number summary: A set of five statistical values that describe a dataset, including the minimum value, first quartile (Q1), median, third quartile (Q3), and maximum value.
6. Percentiles and quantiles: Division of a set of observations into 100 equal parts or into any number of equal parts, respectively.
7. Skewness: A measure of the asymmetry of a probability distribution.

📝 Takeaways from This Article

• From this article, you will get the overview of quartile deviation and all the formulas needed for this calculation.
• You can calculate quartile deviation by using INC and QUARTILE.EXC functions.
• Alternatively, you can calculate the QD value using the generic formula.
• In this article, you will get the automated calculator of quartiles in Excel for both grouped and ungrouped data.
• Also, you can use the VBA macro to calculate the QD value for large number of ungrouped data.
• Finally, you will get 2 examples that you can practice for better understanding.

Conclusion

In this article, you have found how to calculate quartile deviation in excel with various methods for both grouped and ungrouped data. 2 solved examples are also given for a better understanding of readers. I hope you found this article helpful. You can visit our website ExcelDen to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.