For any given dataset, range tells us a simple picture that is what is the spread of values across the dataset.

So you can also say, range represents collection of values between the maximum & minimum value within the data set. In mathematical language, it is the difference between the maximum and minimum value from a dataset. The excel range formula is as follows:

Range=Maximum value – Minimum value

There is no separate excel range function which instead we have to use a combination of different functions. Let’s see the various methods of excel range calculation:

Method 1: Using Min & Max function

1. First select the cell, where you want to display the minimum value from the range. Then type =MIN(array). Here replace array with the range of cells. Just select the range of cells by dragging the cells. This functions tells excel to find the minimum value from the range of cells.
   
   
   
2. Now select the cell, where you want to display the maximum value from range. Type =MAX(array). Again, here also replace array with the range of cells. This functions tells excel to find the minimum value from the range of cells.
   
   
   
3. Now select the final cell where you want to calculate the range value. Here type =E5-E4. Which will subtract the values in E5(max) and E4(min).
   
   
   
4. One other faster way to do this, is to just type one formula. Type =MAX(array)-MIN(array). In just one formula you’ll get the value of range.
   
   
   

Method 2: Excel Range in Formula for Top k & Bottom K values

Let’s say now you’re not just interested in the largest or smallest value from a given range. You want to calculate the range from the second largest & second smallest value, in such a case we can use SMALL() & LARGE() function.

1. First select the cell where we want to get the second smallest value. Now type =Small(array,2). Replace array with the range of cells, here 2 tells excel to get the second smallest number.
   
   
   
2. Select the cell for the second largest value. Now type =Large(array,2).
   
   
   
3. Now select the final cell where we want value of range. Type =E5-E4.
   
   
   
4. Just like before you can do this much faster, if you just type =Large (array,2)-Small(array,2).
   
   
   

Method 3: Calculate range in excel using Conditional Max & Min Formula

Let’s say you have a list of different products with different quantity sold. You have to calculate range from quantity but for only certain products.
When you want to calculate the minimum & maximum value based on certain criteria, we use conditional max & conditional min.

The problem there is no function for min if or max if in excel. So here we have to use a combination of If() & Max() together.

1. Now we have 2 columns, one having different products and one having quantity sold in different countries. We only want to calculate the range in excel for product ‘Laptop’. Now first select the cell where we want minimum value for laptops, type =MIN(IF(A2:A17=”Laptop”,B2:B17)
   
   A2:A17 is the range of cells having different product values & B2:B17 have different quantity.
   
   Here first the inner function IF started where it fetched values from A2:A17 which we’re equal to “Laptop” i.e where the product was laptop and for those products it fetched the different cells from B2:B17.

   Then MIN function ran on those fetched values to find the smallest value.

   Please Note: When you press entre, chances are you might see the value 0 or error #VALUE! Just go back to editing the formula and remember to press CTRL+SHIFT+ENTRE

2. Now we’ll repeat the same to find maximum value for laptops. Type =MAX(IF(A2:A17=”Laptop”, B2:B17)
   
   
   
3. Now the last step is to find range. Type =E5-E4
   
   
   
4. You can do this faster by using just one formula. Type=  MAX(IF(A2:A17=”Laptop”, B2:B17)- MIN(IF(A2:A17=”Laptop”, B2:B17)

Conclusion

Range in excel is just the difference between the maximum & minimum value. I hope this article was helpful to you and explained you how to calculate range in excel and different methods to do so.

If you have any questions or tips, I’d love to hear them in the comment section below.