How to Calculate Range in Excel (5 Handy Methods)

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.

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.