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.

how to calculate range in excel


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 “”.

how to calculate range in excel using MAX and MIN functions

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

how to calculate range in excel using MAX and MIN functions

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.

how to calculate range in excel using MAX and MIN functions

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

how to calculate range in excel using MAX and MIN functions


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.

how to calculate range in excel using MAX and MINIFS functions

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.

how to calculate range in excel using MAX and MINIFS functions

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

how to calculate range in excel using MAX and MINIFS functions

We can use Fill Handle to find the range of Sale of Bags among cells D5 to D15 in cell D16.

how to calculate range in excel using MAX and MINIFS functions

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.

how to calculate range in excel using MAX, MIN and IF functions

We’ll get the Conditional Range in the C16 cell.

how to calculate range in excel using MAX, MIN and IF functions

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

how to calculate range in excel using MAX, MIN and IF functions

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.

how to calculate range in excel using LARGE and SMALL functions

Here’s the result.

how to calculate range in excel using LARGE and SMALL

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

how to calculate range in excel using LARGE and SMALL functions


Method 5 – Applying VBA to Calculate the Range

  • Go to Formulas and select Name Manager.

using VBA

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

using VBA

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

using VBA

  • Go to the Developer tab and then Visual Basic.

using VBA

  • Click on Insert and select Module.

using VBA

A module will appear like this.

using VBA

  • 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

using VBA

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.

using VBA

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

using VBA


Download the Practice Workbook


Range Formula in Excel: Knowledge Hub


<< Go Back to Excel Range | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Shajratul Alam Towhid
Shajratul Alam Towhid

Md Shajratul Alam Towhid, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, holds a pivotal role as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep passion for research and innovation, he actively engages with Excel. In his capacity, Towhid not only adeptly tackles complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his unwavering commitment to consistently delivering exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo