This tutorial will demonstrate how to calculate **quartile deviation** in Excel. It is essential to use **quartile deviation **in almost every aspect of our lives. Quartiles are the most effective when we want to find the center of certain data. This shows us the variability of the values of certain datasets. We can use it in case of sales in the company, marks, etc. So, it is important to learn how to calculate **quartile deviation** in Excel.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from here.

## 4 Effective Methods to Calculate Quartile Deviation in Excel

Weâ€™ll use a sample dataset overview as an example in Excel to understand easily. If you follow the steps correctly, you should learn how to calculate **quartile deviation** in Excel on your own. The methods are

### 1. Calculating Quartile Deviation for Linear Data

In this case, our goal is to calculate **quartile deviation** in Excel for linear data. That means we will have a sample dataset where we will have data for every certain heading. We can learn this method by following the below image.

**Steps:**

- First, arrange the dataset similarly to the below image. For instance, we have
**Day**and**Frequency**in columns**B**and**C**.

- Next, insert the following formula in the
**C16Â**cell.

`=C6+0.75*(C7-C6)`

- After that, you will get the
**First Quartile**by using the above formula.

- Subsequently, insert the following formula in the
**C17Â**cell.

`=C12+0.25*(C13-C12)`

- Therefore, you will get the
**Third Quartile**by using the above formula.

- Moreover, insert the following formula in the
**C18Â**cell.

`=(C17-C16)/2`

- Next, you will get the
**Quartile Deviation**by using the above formula.

- In addition, insert the following formula in the
**C19Â**cell.

`=(C17-C16)/(C17+C16)`

- Finally, you will get the
**Coefficient of Quartile Deviation**by using the above formula.

Hence, you have calculated the **quartile deviation** in Excel for linear data.

### 2. Determining Quartile Deviation for Grouped Data

Now, we want to calculate the **quartile deviation in Excel** for grouped data. That means we will have a sample dataset where we will have ranged data that will have class intervals. We can learn this method by following the below image.

**Steps:**

- First, arrange the dataset like the image below. In this case, we have
**Class Interval**in**Column B**,**Frequency (F)**in**column D**, and**Cumulative Frequencies**in**column E**.

- Secondly, insert the following formula in the
**E5Â**cell.

`=D5`

- Thirdly, you will get the first cumulative frequency which is the same as the first value of the frequency.

- Afterward, insert the following formula in the
**E6Â**cell.

`=E5+D6`

- Next to that, you will get the second cumulative frequency by adding the new frequency with the previous frequency.

- In addition, you will get the result for this cell and then use the
**Fill Handle**to apply the formula to all cells.

- Moreover, the
**Total Frequency**will be the total value of**Cumulative Frequency**.

- Thereafter, insert the following formula in the
**D12**cell to calculate the**Class Width**.

`=B6-B5`

- Hence, you will get the result by subtracting the values.

- Furthermore, to calculate the quartiles, you have to create two columns named
**Q1**and**Q3**. Each quartile consists of**iN/4**,**CF**,**Position of CF**,**L**,**F**,**M**, and**Value of Q1**or**Q3**.

**L** presents the lower value of the interval that consists of the quartile cumulative frequency,

**C** represents the width of the class,

**F** represents the frequency of the interval,

**N** is the total frequency and

**M** is the cumulative frequency leading up to the interval.

- Along with this, insert the following formula in the
**E16Â**cell.

`=D13/4`

- Continuously, you will get the result by using division.

- Again, insert the following formula in the
**E17Â**cell.

`=MIN(FILTER(E5:E10,E5:E10>=E16))`

Note that, as we found** (iN/4)** value is **12.5** so we have filtered the **E5 to E10** cells according to that condition by using **the FILTER function** and found the minimum value from it by using **the MIN function**.

- So, you will get the result by using a combination of
**MIN**and**FILTER**So, you got result**13**in this case.

- Then, insert the following formula in the
**C18Â**cell.

`=MATCH(E17,E5:E10,0)`

Note, we have **the MATCH function** to find the position of CF. We have applied the function on range **E5 to E10** and it will look up with the help of the **E17** cell.

- Next, you will get the result by using the
**MATCHÂ**function.

- After that, insert the following formula in the
**E19Â**cell.

`=INDIRECT(ADDRESS(ROW(E4)+E18,2))`

Note, first we have selected the rows by using the ROW function. After that, we used **the ADDRESS function** to select the row and column reference accordingly. And **INDIRECT function** shows the reference cells overall.

- Moreover, you will get the result by combining
**INDIRECT**,**ADDRESS,**and**ROWÂ**functions.

- Furthermore, insert the following formula in the
**E20Â**cell.

`=INDIRECT(ADDRESS(ROW(E4)+E18,4))`

- Afterward, you will get the result by the combination of
**INDIRECT**,**ADDRESS,**and**ROWÂ**functions.

- Thereafter, insert the following formula in the
**E21Â**cell.

`=INDIRECT(ADDRESS(ROW(E4)+E18-1,5))`

- Consequently, you will get the result by combining
**INDIRECT**,**ADDRESS,**and**ROWÂ**functions.

- Hence, insert the following formula in the
**E22Â**cell.

`=E19+(D12/E20)*(E10/4-E21)`

- As a result, you will get the
**Value of Q1**in the**E22Â**cell.

- Subsequently, In this case, you need to multiply the new formula by
**3**to find the third quartile. We will use the same formula as**Q1**but this time we have to multiply by**3**. The formula becomes,**(iN/4)=3*D13/4=37. 5**

Then, if you repeat the same process, we will get the result for the **Third Quartile of Q3** as well.

- Consequently, insert the following formula in the
**E24Â**cell.

`=(C22-E22)/2`

- Moreover, you will get the
**Quartile Deviation**by using division.

- In addition, insert the following formula in the
**E25Â**cell.

`=(C22-E22)/(C22+E22)`

- Last, you will get the
**Coefficient of Quartile Deviation**by using division.

Therefore, we have calculated the **quartile deviation** in Excel for grouped data.

**Read More:** **How to VLOOKUP Minimum Value in Excel (Easiest 8 Ways)**

**Similar Readings**

**How to Copy Cell Address in Excel (5 Simple Ways)****Example of Cell Address in Excel (5 Ideal Cases)****How to Return Cell Address of Match in Excel (3 Easy Ways)****How to Find Lowest 3 Values in Excel (5 Easy Methods)****How to Calculate Average, Minimum And Maximum in Excel (4 Easy Ways)**

### 3. Applying Excel QUARTILE.INC Function to Calculate Quartile Deviation

**At this point**, we want to calculate the **quartile deviation** in Excel by using **QUARTILE.INC**Â **function**. The **QUARTILE.INC** function is a built-in function in Excel that is categorized as a Statistical Function. **Quartiles** are values that split your data into quarters. It divides your data into four segments according to where the numbers fall on the number line. It is one of those essential functions. We can learn this method by following the below steps.

**Steps:**

- To begin with, arrange the dataset like the below image. In this case, we have
**Name**in column**B**and**Time**in Column**C**. We want to find the**Quartiles**in**Column E**.

- In addition, insert the following formula in the
**F4Â**cell.

`=QUARTILE.INC(C5:C14,1)`

- Furthermore, you will get the result by using the
**First Quartile Q1**.

- Moreover, insert the following formula in the
**F5Â**cell.

`=QUARTILE.INC(C5:C14,3)`

- Furthermore, you will get the result by using the
**Third Quartile Q3**.

- Subsequently, insert the following formula in the
**F6Â**cell.

`=(F5-F4)/2`

- Finally, you will get the result by using the
**Quartile Deviation**.

**Read More: How to Use AVEDEV Function in Excel (2 Suitable Examples)**

### 4. Using AGGREGATE Function

Now, we want to calculate the **quartile deviation** in Excel by using **the AGGREGATE** **function**. In Excel, the **AGGREGATE** function is used on different functions to get specific results. We can learn this method by following the below steps.

**Steps:**

- First, arrange the dataset like the below image. We have
**Name**and**Marks**in columns**B**and**C**.

- Secondly, insert the following formula in the
**C10Â**cell.

`=AGGREGATE(17,4,C5:C9,1)`

Note that The **QUARTILE** function accepts five values,

**0** =** Minimum value**

**1 **=** the First quartile, 25th percentile**

**2 **=** the Second quartile, 50th percentile**

**3 **=** the Third quartile, 75th percentile**

**4 **=** Maximum value**

The function works with **QUARTILE.INC** (function number 17) and** QUARTILE.EXC** (function number 19) function to produce the quartile results.

- Thirdly, you will get the result by using the
**AGGREGATEÂ**function.

- Moreover, insert the following formula in the
**C11Â**cell.

`=AGGREGATE(19,4,C5:C9,3)`

- Consequently, you will get the result by using the
**EXC**.

- Furthermore, insert the following formula in the
**C12Â**cell.

`=(C11-C10)/2`

- Lastly, you will get the result by using the
**Quartile Deviation**.

**Read More: How to Set a Minimum and Maximum Value in Excel (6 Easy Methods)**

## Conclusion

Henceforth, follow the above-described methods. These methods will help you to calculate **quartile deviation** in Excel. We will be glad to know if you can execute the task in any other way. Follow the **ExcelDemy** website for more articles like this. Please feel free to add comments, suggestions, or questions in the section below if you have any confusion or face any problems. We will try our best to solve the problem or work with your suggestions.

## Related Articles

**How to Return Cell Address Instead of Value in Excel (5 Ways)****Use Cell Address in Excel Formula (4 Useful Methods)****How to Get Cell Value by Address in Excel (6 Simple Methods)****Reference Cell by Row and Column Number in Excel (4 Methods)****How to Find Minimum Value Based on Multiple Criteria in Excel****Calculate Absolute Deviation in Excel (2 Useful Examples)**