Range in statistics is the difference between the highest value and lowest value of a set of data. We need to apply range in the real work fields a lot during our everyday working hours for different purposes. And this thing becomes easier when we apply this using Excel by knowing how to calculate range in Excel. In this article, we’ll discuss 5 methods on how to calculate range in Excel.

## 5 Methods to Calculate Range in Excel

We can find the range of a dataset using Excel by using various formulas. To understand the range better we have made a dataset of **Sales of Shoes and Bags in 2021** at different stores of a shop in California. The dataset based on the amount of **Sales of Shoes **and **Sales of Bags** is like this.

**1. Applying MAX and MIN Functions to Calculate Range in Excel**

This method is applied when we need to calculate range only without any condition. We can write the formula for the **C16** cell of the below picture like this.

`=MAX(C5:C15)-MIN(C5:C15)`

Here, **C5:C15** refers to the amount of *Sales of Shoes* from the **C5** cell to the **C15** cell. **C16** refers to the range of **C5** to **C15** cells. The **MAX function** refers to the maximum value among cells **C5** to** C15** and **MIN **function refers to the minimum value among **C5** to** C15 **cells**.**

The **MAX** function first finds out the maximum value among cells **C5** to **C15** and **MIN** function finds out the minimum value among cells **C5** to **C15**. Then the maximum value is subtracted from the minimum value by the subtraction sign “**–**”.

As a result, we find the range of **Sales of Shoes** in the **C16 **cell i.e. 8692.

Similarly, we can find the range of **Sale of Bags** among **D5** to **D15 **cells in cell **D16** where we have basically used the same formula for the **C16** cell by using the **Fill Handle**. We need to drag the **C16** cell towards the right like this.

And finally, we get the value of range in the **D16 **cell.

**2. Using MAX and MINIFS Functions to Calculate Conditional Range**

When we have to find a conditional range we then need to use the **Conditional Range** formula. The conditional range is basically finding the range with a condition when some values in the dataset are excessively smaller or larger than most of the values. If we take those smaller or larger values we can’t find out the true practical range. In these cases, we can use formulas of conditional range.

We can use** MAX** and **MINIFS **functions in **Excel 2019** and** Microsoft 365**. Because **MAX **and **MINIFS** functions are not available in the other versions of Excel. We have made some changes in the dataset to lessen some values to a very lower number than the other values like this.

We can write the formula for the **C16** cell in the picture above like this.

`=MAX(C5:C15)-MINIFS(C5:C15,C5:C15,”>1000”)`

The **MINIFS** function finds out the minimum value among cells **C5** to **C15** where there is a condition that the minimum value should be greater than **1000**.

Then, we get the *Conditional Range* in cell **C16 **by pressing **ENTER.**

At last, we can use **Fill Handle** to find the range of **Sale of Bags** among cells **D5** to **D15** in cell **D16.**

In the same way, we can use the **MAXIFS** function when we need to put a condition on the opposite side i.e. boundary limit to the maximum value.

**3. Utilizing MAX, MIN, and IF Functions to Calculate Conditional Range**

We can simply use **MAX**,** MIN**, and **IF **functions to calculate the *Conditional Range*. We can write the formula for the **C16** cell in the picture above like this.

`=MAX(C5:C15)-MIN(IF(C5:C15>3000,C5:C15))`

Here, the **IF** function basically puts a condition that lies inside the bracket and the condition fixes that the lowest value taken should be greater than **3000,** and the **MIN** function finds out the minimum value among cells **C5** to **C15** which is again conditional by the **IF** function.

Eventually, we’ll get the *Conditional Range* in the **C16 **cell.

After dragging the formula horizontally, we’ll get the *Conditional Range* in the case of the *Sales of Bags* column.

**Read More: **How to Calculate Time Range in Excel

**4. Using LARGE and SMALL Function to Calculate Range**

We can use another method which is by the usage of **LARGE** and **SMALL** functions. We can write the formula for the **C16** cell in the picture above like this.

`=LARGE(C5:C15)-SMALL(C5:15,1)`

Here, the **LARGE** function first finds out the larger value among cells **C5** to **C15,** and the **SMALL** function finds out the smaller value among cells **C5** to **C15**. In this formula, **1 **refers to the smaller value that should be the first smallest one.

And lastly, we need to press **ENTER **to get the range in the **C16** cell.

Like the same way of the previous methods, we can again use** Fill Handle** to find the range of **Sale of Bags** among cell **D5** to **D15** in cell **D16.**

**5. Applying VBA to Calculate Range**

We can also use **VBA **code for finding the range in a dataset. For using code we need to follow the below steps.

First, go to **Formulas** > **Name Manager**.

As a result, we’ll find an edit bar where we need to fill up **Name** and **Refers to **options. In the **Refers to** box, we need to select cells from **C5** to **C15** like this.

We need to follow the same steps for another column i.e. column on **Sales of Bags**.

After that, we should click the **Developer tab** > then **Visual Basic.**

In this step, we need to click **Insert** > click **Module**.

As a result, a module will appear like this.

Then, we need to copy and paste the following code into the module.

```
Function RangeS(Sales_Shoes)
RangeS = Application.WorksheetFunction.Max(Sales_Shoes) _
- Application.WorksheetFunction.Min(Sales_Shoes)
End Function
Function RangeB(Sales_Bags)
RangeB = Application.WorksheetFunction.Max(Sales_Bags) _
- Application.WorksheetFunction.Min(Sales_Bags)
End Function
```

Here, the first function is created for determining the range in the case of *Sales of Shoes*. And, the second function is for finding the range of *Sales of Bags*.

And finally, we will find our calculated range for *Sales of Shoes* using the **VBA** code like this.

Also, we will find our calculated range for *Sales of Bags* using the **VBA** code like this.

**Read More:** How to Calculate Range for Grouped Data in Excel

## Things to Remember

- We can not apply
**MAX**and**MINIFS**functions that are not applicable in the early version of Excel except Excel 2019 and Microsoft 365. - We need to fix brackets, commas, and inverted commas properly to get the final result of the range.
- We can insert any number instead of
**1**in the**LARGE**and**SMALL**functions, this number basically indicates which lowest or highest value needs to be taken to calculate the range. If it is**3**then it means that the formula will take the 3rd lowest value in the calculation.

**Download Practice Workbook**

## Conclusion

Calculating range is a very important calculation in our daily work. By using Excel with different formulas we can calculate the range very easily.

## Range Formula in Excel: Knowledge Hub

- How to Calculate Percentage Range in Excel
- How to Calculate Moving Range in Excel
- How to Calculate Bin Range in Excel
- Calculate Interquartile Range in Excel

**<< Go Back to Excel Range | Learn Excel**