Shajratul Alam Towhid
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.
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.
Read More: How to Calculate Average True Range in Excel (with Easy Steps)
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.
Read More: How to Calculate Moving Range in Excel (4 Simple Methods)
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 Handy Methods)
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 (3 Effective Methods)
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.
