We have made a dataset of **Sales of Shoes and Bags in 2021** at different stores. We’ll use it to apply various range functions.

**Method 1 – Applying MAX and MIN Functions to Calculate a 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.

We can also find the range of **Sale of Bags** from **D5:D15 **in cell **D16.**

We used the Fill Handle from C16 and dragged it to D16.

We get the value of the range in the **D16 **cell.

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

**MINIFS** function is available in Excel 365 and Excel 2019 onward.

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

We get the *Conditional Range* in cell **C16 **by pressing **ENTER.**

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.

**Method 3 – Utilizing MAX, MIN, and IF Functions to Calculate a Conditional Range**

Here’s a formula that also calculates a range of values.

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

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.

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

**Method 4 – Using LARGE and SMALL Functions to Calculate the Range**

We used the following formula in C16:

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

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.

Here’s the result.

We can apply the similar function in D16 by dragging the Fill Handle.

**Method 5 – Applying VBA to Calculate the Range**

- Go to
**Formulas**and select**Name Manager**.

- You’ll get an edit bar to fill up
**Name**and**Refers to**options. - In the
**Refers to**box, select cells from**C5**to**C15**.

- Repeat the process to name column D as
**Sales of Bags**.

- Go to the
**Developer tab**and then**Visual Basic.**

- Click on
**Insert**and select**Module**.

A module will appear like this.

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

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

- Use the functions as if they were Excel functions by putting their name in the cell and naming the range they apply to as the argument.

- We will find our calculated range for
*Sales of Bags*using the**VBA**code like this.

**Download the Practice Workbook**

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