Spreadsheets are used to calculate complex numbers very easily. But what if you want to calculate the range of your sales, income, profits, etc. then the formula in Excel is said to be your best friend.

How to calculate range in Excel? It is interesting to see the most effective formulas are right on the tip of your tongue. The range is one of them.

Every spreadsheet that is created by Microsoft Excel includes a very useful feature. This feature is used to enter different values within the same spreadsheet.

The range available in Excel is displayed in the form of cells. Each cell is displayed with its coordinates. The coordinate is called row number and column number.

Both row number and column number of each cell are displayed in the form of a very. In this blog, we’ll discuss how to calculate the range value in an excel worksheet.

What is Range?

For any given data set, the range of that data set would be the range of values contained inside that data set.

In a very simple example, if you have a data collection of student scores in which the smallest score is 15 and the maximum score is 98, the spread of this data set (also known as the range of this data set) would be equal to around 73.

98 – 15 is the range of values.

When it comes to data sets, the term “range” simply refers to the difference between the maximum and minimum value of that data collection.

Steps of How to Calculate Range in Excel?

Using a list of sorted values, you just subtract the first value from the last value to arrive at the final result (assuming that the sorting is in the ascending order).

However, in the vast majority of circumstances, you would be dealing with a random data collection that has not been previously sorted.

Finding the range of values in such a data set is also a basic process.

Excel includes tools for determining the maximum and minimum values from a range of values (the MAX and the MIN function).

Assume you have a data set similar to the one shown below, and you want to determine the range of values for the data in column B.

In Excel, use a dataset to find a range.

The following is the formula for determining the range of values for this data set:

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

The formula above finds the maximum and least values and then calculates the difference between them.

Calculate the Conditional Range in Excel

In most practical situations, determining the range would not be as straightforward as just subtracting the least number from the greatest value.

In real-world circumstances, you may additionally have to take into consideration certain variables or outliers.

For example, you may have a data collection in which all of the values are less than 100, but there is one value that is greater than 500 in size.

If you try to calculate the arrangement for this data set, you will end up making incorrect interpretations of the data.

The good news is that Excel contains numerous conditional formulas that can assist you in sorting out some of the irregularities.

As seen below, there is a data set in which you must determine the range of sales values for column B values.

In Excel, use a dataset to find a range.

If you look closely at this data, you will find that there are two stores where the results are significantly lower than the national average (Store 1 and Store 3).

Because they are new locations, or because there were external circumstances that had an impact on sales in these specific stores, this could be the case.

In order to calculate the range for this data set, it may be necessary to eliminate these newer establishments and only evaluate stores with a significant amount of sales.

Take, for example, the scenario in which ignore any stores with sales of less than 20,000 dollars.

The following is the formula that would be used to find the range associated with the condition:

RANGE=MAX(B2:B11)-MINIFS(B2:B11,B2:B11,”>20000″)

Excel formula for calculating conditional range

Instead of using the MIN function in the preceding formula, use the MINIFS function (which is a new function in Excel 2019 and Microsoft 365), which is more efficient.

If all of the criteria listed in the function are met, this function returns the smallest possible value. In the preceding formula, it is indicated that the requirement would be any value greater than 20,000 dollars.

As a result, when determining the minimum value, the MINIFS function looks through the full data set, but only examines those values that are greater than 20,000 in magnitude.

This ensures that values less than 20,000 are disregarded and that the minimum value is always more than 20,000, as specified (hence ignoring the outliers).

It should be noted that the MINIFS function in Excel is a new feature that is only available in Excel 2019 and Microsoft 365 subscriptions. If you’re using an earlier version, you won’t be able to use this feature (and can use the formula covered later in this tutorial)

Instead of using the MINIF function in your Excel, you can use the following formula, which is a mix of the IF function and the MIN function, to get the same result:

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

When dealing with outliers in one direction, you can use the conditional MINIFS function to avoid data points that are outliers in the opposite direction, (i.e., a couple of large data points that can skew the data).

FAQs Related to the Topic

Is a range can be used in a formula?

If you want to avoid using cell address references in computations and formulas, you can use ranges instead.

Although you can assign several names to the same cell or range of cells, if you use the same name to specify two different ranges, the previous name is replaced by the newer name.

How do you create a bin range in Excel?

Type the input data in one column of a worksheet, and the bin numbers in ascending order in another column of the same worksheet.

Select Data > Data Analysis > Histogram > OK from the drop-down menu. Select the input range (your data) from the Input drop-down menu, then the bin ranges from the Bin drop-down menu.

Conclusion

In this blog, you have learned how to calculate a range in excel. We have many options in excel to calculate a range in a worksheet. This blog has a set of formulas that helps you to compute the range in a worksheet.

If you want to know more tricks about Microsoft Excel visits our website.