How to Calculate Range in Excel (5 Handy Methods)

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.


Download Practice Workbook


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.

how to calculate range in excel


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

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

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.

how to calculate range in excel using MAX and MIN functions

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

how to calculate range in excel using MAX and MIN functions


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.

how to calculate range in excel using MAX and MINIFS functions

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.

how to calculate range in excel using MAX and MINIFS functions

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

how to calculate range in excel using MAX and MINIFS functions

At last, 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.


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.

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

Eventually, 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


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.

how to calculate range in excel using LARGE and SMALL functions

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

how to calculate range in excel using LARGE and SMALL

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.

how to calculate range in excel using LARGE and SMALL functions


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.

using VBA

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.

using VBA

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

using VBA

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

using VBA

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

using VBA

As a result, a module will appear like this.

using VBA

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

using VBA

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.

using VBA

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

using VBA


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.

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.

Towhid
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo