**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.

**Qr **= r’th Quartile Value

**H1 **= Lower Limit of Quartile Class

**H2 **= Higher Limit of Quartile Class

**N **= Number of observations in Dataset

**F **= Frequency of Quartile Class

**C **= Cumulative Frequency till the Quartile Class

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.

**Method**

### 1. Using **QUARTILE **Function for Getting Quartile Value

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:

**QUARTILE.INC(array, quart)**

**QUARTILE.EXC(array, quart)**

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.

__Difference Between QUARTILE.EXC and QUARTILE.INC in Excel__

There is some slight differences between these functions. The differences between them are given as follows:

**INC**function calculates the percentile range including 0 and 1 but**QUARTILE.EXC**excludes 0 and 1 while calculating.**INC**function includes the**median**value in the calculation but the other doesn’t.**EXC**function has always the**“greater than”**like behavior but the other has either**“greater than”**or**“equal to”**behavior.- You can use
**INC**function for calculating the maximum or minimum value but you can’t use**QUARTILE.EXC**function for this.

Here is a complete image showing all formulas used at a glance. So, you will get an idea of how we have calculated the quartiles.

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.

** **__Important Notes About the QUARTILE.INC Function __

While using the **QUARTILE **function, you have to keep something in mind. These are as follows:

- You have to insert the quart value as an
**integer**. - The
**quart**value must be either 1,2,3 or 4. It can’t be 0 or any other value. If you insert any other value then, it will return the**#NUM** - Also, if you insert any
**non-numeric**value as quart it will return the**#VALUE**

**Method**

### 2. Using Basic Mathematical Formula

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:

**✅ Notes: **

- This is a very simple and user-friendly method and you will get the accurate value.
- But, this is a manual method, so you have to identify the quartile rows and insert their cell reference individually. For, large datasets, it might be difficult to apply.

**Method**

### 3. Create Dynamic Solution for Quartile Calculation Using Excel Functions

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**:

** ****⛏️ Formula Breakdown:**

Here, I have used the formula “**Qr= r*(n+1)/4**” to get the position of quartile. Here, **r** is the number of quartiles. And, **n** is the number of observations. I have calculated it using the **COUNT **function.

- 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**:

** **

**⛏️ Formula Breakdown:**

Here, the **INDEX **function is used to get the value of a specific serial number given for a dataset. Syntax of **INDEX **Function is:

**=INDEX (array, row_num, [column_num])**

For example **INDEX(A1:A10, 5) **will give the 5th value of the selected data range **A1:A10. **Using a similar formula in cells **E34 **and **E35**, we will get the **15th **and **16th **observation values.

** ****📌 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:**

If I insert the value of the cells which are referred in the given formula then the formula will look like:

**=0.743+(15.75-15)*(0.753-0.743)**

Thus, we are getting the quartile 1 or the **5.25th** observation value of the dataset by interpolation formula.

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.

**Method**

### 4. Using AGGREGATE Function

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.

__Overview of AGGREGATE Function__

With this function, you can do the works of many functions like **SUM**, **AVERAGE**, **COUNT**, **MIN**, **MAX**, **QUARTILE**, etc. For this, just you have to insert the function number in the argument. Syntax of the **AGGREGATE **function is as follows:

1. Syntax with References

**=AGGREGATE(function_num, options, ref1, ref2, …)**

2. Syntax with References

**=AGGREGATE(function_num, options, array, [k])**

**Arguments**:

**Function_num :**It is required one. And after inserting the “**=AGGREGATE**” word in the cell, you will see the list of functions. So, you can select the function from the list or you can insert the function number manually. Here I am giving the list of function numbers with their work in the following image.

**Options:**It is also a required one. There are 7 options that you can choose from. Also, I am giving you the list for your better understanding.

**ref1**= It is also a required one for**reference type**syntax of this function. The first numeric argument for functions to perform the operation. It could be one single value, array value, cell reference etc.**ref2**= It is Optional, it could be numeric values from**2**to**253**.**array**= It is also a required one for the**array type**syntax of this function. It is the range of numbers or cell references based on what the functions will perform.**[k]**= It is also an optional one for the**array type**syntax of this function. This argument is needed only when performing with the function number from**14**to**19**(see the function_num table).

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:

**⛏️ Formula Breakdown:**

Here,

**17**is the function number that denotes the quartile function including the median value.**4**is the option number that says to ignore nothing while calculating.**G5:G24**is the selected data range for calculation.- Lastly,
**1**and**3**denote the quartile and quartile 3 commands.

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.

**⛏️ VBA Code Breakdown:**

** ***Sub QuartileDeviation()*

- At first, I created a sub named 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*

- Then, I created the necessary dimensions required for the sub.

* Set data = Application.InputBox(Prompt:=”Select the range of data to calculate the quartile deviation for:”, Type:=8)*

- Now, I create an input box that will ask the user to select the data range for quartile calculation.

* n = data.Count*

* ReDim Values(1 To n)*

- By this, assign n as the number of observations of the dataset.

* For i = 1 To n*

* Values(i) = data(i).Value*

* Next i*

- Now, assign the range of data to specific dimensions.

* 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*

- Here, I have assigned the necessary formulas of mean, median, Q1, Q3, and QD.

* Range(“K6”).Value = Q1*

* Range(“K7”).Value = Q3*

* Range(“K9”).Value = IQR / 2*

- Finally, I have inserted the Quartile values in the designated cells in the active worksheet

*End Sub*

- At last, close the sub.

## 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.

**Method**

### 1. Using Basic Formula and Inserting Parameters Manually

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.

**Method**

### 2. Creating Automated Quartile Deviation Calculator for 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.

**Example**

### 1. Calculate Quartile Deviation of 12 Years Sales Data

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.25**th 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.

**Example**

### 2. Calculate Quartile Deviation of Accident Frequencies of Different Ages

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.

- Measures of central tendency:
**Mean:**The average value of some specified numbers.**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**.**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.

- Measures of dispersion:
**Range**: Denotes a dataset that maintains a similar type. This range is varied within a given limits.**Variance:**Means how scatter the points of a data from their average value.**standard deviation**: It is similar thing to Variance. Basically, the square root of variance is the standard deviation.

**Box plot**: A graphical representation of quartile deviation.**Outliers**: Data points that fall outside the range defined by the quartile deviation.**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.**Percentiles and quantiles**: Division of a set of observations into 100 equal parts or into any number of equal parts, respectively.**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.