In the above formula, I specified the criteria to be any value that is more than 20,000. This function finds the minimum value if the criteria mentioned in it are met. In the above formula, instead of using the MIN function, I have used the MINIFS function (it’s a new function in Excel 2019 and Microsoft 365). In this example, let’s say I want to ignore all those stores where the sales value is less than 20,000.īelow is the formula that would find the range with the condition: =MAX(B2:B11)-MINIFS(B2:B11,B2:B11,'>20000') While calculating the range for this data set, it might make sense to exclude these newer stores and only consider stores where there are substantial sales. This could be because these are new stores or there were some external factors that impacted the sales for these specific stores. If you look closely at this data, you would notice that there are two stores where the values are quite low (Store 1 and Store 3). Thankfully, Excel has many conditional formulas that can help you sort out some of the anomalies.īelow I have a data set where I need to find the range for the sales values in column B. If you calculate arrange for this data set, it would lead to you making misleading interpretations of the data. In real-life scenarios, you might also need to account for some conditions or outliers.įor example, you may have a data set where all the values are below 100, but there is one value that is above 500. In most practical cases, finding the range would not be as simple as just subtracting the minimum value from the maximum value

Quite straightforward… isn’t it? Calculate Conditional Range in Excel The above formula finds the maximum and the minimum value and gives us the difference. Suppose you have a data set as shown below, and you want to calculate the range for the data in column B.īelow is the formula to calculate the range for this data set: =MAX(B2:B11)-MIN(B2:B11) If you have 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 order).īut in most cases, you would have a random data set where it’s not already sorted.įinding the range in such a data set is quite straightforward as well.Įxcel has the functions to find out the maximum and the minimum value from a range (the MAX and the MIN function). ‘Range’ is nothing but the difference between the maximum and the minimum value of that data set.

To give you a simple example, if you have a data set of student scores where the minimum score is 15 and the maximum score is 98, then the spread of this data set (also called the range of this data set) would be 73 Range = 98 – 15 In a given data set, the range of that data set would be the spread of values in that data set. In this tutorial, I will show you really simple ways to calculate the range in Excel. Normally, when I use the word range in my tutorials about Excel, it’s a reference to a cell or a collection of cells in the worksheet.īut this tutorial is not about that range.Ī ‘Range’ is also a mathematical term that refers to the range in a data set (i.e., the range between the minimum and the maximum value in a given dataset)

Range in math definition