How To Find A Range In Excel

Normally, when I use the give-and-take range in my tutorials nearly Excel, it'southward a reference to a cell or a collection of cells in the worksheet.

But this tutorial is non most that range.

A 'Range' is also a mathematical term that refers to the range in a data fix (i.e., the range between the minimum and the maximum value in a given dataset)

In this tutorial, I will show y'all really simple ways to calculate the range in Excel.

What is a Range?

In a given data set, the range of that data fix would be the spread of values in that data set.

To requite you a elementary example, if yous have a information set of pupil scores where the minimum score is fifteen and the maximum score is 98, then the spread of this data fix (also called the range of this information prepare) would exist 73

Range = 98 – 15

'Range' is nothing merely the difference between the maximum and the minimum value of that data set.

How to Calculate Range in Excel?

If you accept a list of sorted values, you just have to subtract the first value from the last value (assuming that the sorting is in the ascending club).

Simply in most cases, you lot would have a random data set where information technology's not already sorted.

Finding the range in such a data prepare is quite straightforward as well.

Excel has the functions to find out the maximum and the minimum value from a range (the MAX and the MIN role).

Suppose you lot have a data set as shown below, and you want to calculate the range for the data in cavalcade B.

Beneath is the formula to calculate the range for this data set:

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

The above formula finds the maximum and the minimum value and gives us the difference.

Quite straightforward… isn't information technology?

Summate Conditional Range in Excel

In most applied cases, finding the range would not be every bit simple equally just subtracting the minimum value from the maximum value

In real-life scenarios, you might also need to account for some weather or outliers.

For instance, you may take a data gear up where all the values are below 100, but there is ane value that is to a higher place 500.

If y'all calculate conform for this information set, information technology would lead to you lot making misleading interpretations of the data.

Thankfully, Excel has many conditional formulas that can aid you sort out some of the anomalies.

Below I have a information set where I need to find the range for the sales values in cavalcade B.

If you wait closely at this data, you would discover that there are 2 stores where the values are quite low (Store i and Store 3).

This could be because these are new stores or there were some external factors that impacted the sales for these specific stores.

While computing the range for this data prepare, it might brand sense to exclude these newer stores and only consider stores where at that place are substantial sales.

In this example, let'south say I want to ignore all those stores where the sales value is less than 20,000.

Below is the formula that would find the range with the condition:

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

In the higher up formula, instead of using the MIN function, I take used the MINIFS function (it's a new function in Excel 2019 and Microsoft 365).

This function finds the minimum value if the criteria mentioned in information technology are met. In the above formula, I specified the criteria to be any value that is more than than 20,000.

Then, the MINIFS function goes through the entire data set, but only considers those values that are more than xx,000 while calculating the minimum value.

This makes sure that values lower than 20,000 are ignored and the minimum value is ever more than xx,000 (hence ignoring the outliers).

Note that the MINIFS is a new function in Excel is available only in Excel 2019 and Microsoft 365 subscription. If you're using prior versions, you would not have this part (and can use the formula covered later in this tutorial)

If you don't have the MINIF function in your Excel, employ the below formula that uses a combination of IF function and MIN role to do the same:

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

Only like I have used the conditional MINIFS function, yous tin also use the MAXIFS office if you want to avert data points that are outliers in the other direction (i.e., a couple of large information points that can skew the information)

And then, this is how you lot can apace detect the range in Excel using a couple of simple formulas.

I hope you institute this tutorial useful.

Other Excel tutorials y'all may like: