It happens that you don’t know how to do things, this mostly happens when you are new at something. If you are new at creating an Excel sheet, then you probably don’t know how to calculate a range in excel. Also, some users find the calculating the range in Excel is somewhere is a time-consuming process.

In this article, we will discuss how to calculate range in Excel sheets quickly. So, make sure to read the article till the end.

What is Range in Excel?

The range is basically a set of a given data which is a difference between the largest and smallest values. Here’s how to know more about the range

Range = maximum value – minimum value

Range = 12-5 = 7

Here, 12 is a maximum value and 5 is the minimum value in the given set. So we need to subtract maximum and minimum values to obtain a range in a given data set. Furthermore, a large range indicates a wide spread of results, and a small range indicates that data is closely centered around a specific value.

How to Calculate Range in Excel?

To calculate a simple range in Excel, we assume that you have a list of sorted values. As shown above, you simply have to subtract the largest value from the smaller value. But it’s not necessary that you will have a sorted data set given in Excel. However, there are functions available that will help you to sort the data which is MAX and MIN function.

Let’s say that we have the following data set, and we have to calculate the range of the data present in (Articles Written (per month).

To calculate the range of a particular column, here’s the formula that we need to use:

=MAX(B2:B11)-MIN(B2:B11)

This formula will present you the maximum and the minimum value and will you the required range. This is the simplest method to calculate range in Excel.

How to Calculate Conditional Range in Excel

Sometimes there come conditions where you want to obtain a range of particular values. In these types of scenarios, we need to change the formula or we need to change the method to calculate conditional range in Excel.

To make you understand, take a look at the article written per month column. You will get to see that article written in the month of Jan and Mar is less than 20,000. Suppose I want to exclude these two rows from excel and calculate the range of the column then we can do it easily by another forula, this is known as calculating a conditional range.

Here’s the formula to exclude the months of Jan and Mar from the column and calculate the range.

=MAX(B2:B11)-MINIFS(B2:B11,B2:B11,">20000")

Here, we have not used any MIN function formula, but the MINIFS function formula which is applicable for Excel 2019 and Microsoft 365.

MINIFS function will just exclude the month of Jan and Mar and calculate the range of reset of the column, that’s because we gave condition in the formula not to calculate the range which varies below 20,000 articles written per month.

Likewise, you can also use the MAXFS function which follows the same method to calculate conditional range to exclude large data points.

=MAX(B2:B11)-MIN(IF(B2:B11>20000,B2:B11))

That’s how you calculate range in Excel without wasting much of your time.

That’s it for now, hope this helps! We are glad to know your thoughts in the comment section below. Till then, stay tuned for more info.