How to Find Range in Microsoft Excel

A data set's range provides a clear picture of the range of values it includes. If you're making anything like a box and whisker plot, you may need to consider the range as a starting point for your data analysis. You can easily find the range using Excel's "MIN" and "MAX" functions.

Range: What Does It Mean?

The range, a straightforward measurement, shows the dispersion of values in a data collection. It is easily defined as follows:

Range = highest value minus lowest value

Thus, if you have a collection of data with the values 4, 2, 5, 8, 12, and 15, the range is equal to 15 minus the value with the lowest value (2). In this instance

Range = 15-2 = 13

According to this example, the data collection has 13 different integers. The ends of the whiskers, which represent the lowest and maximum values in a box and whisker plot, provide a visual representation of the range. A broad range of findings is shown by a huge range, while a narrow range is indicated by data tightly clustered around a single number.

Maximum and Minimum Functions

Obtaining the data set's lowest and maximum values is the key to identifying a range in Excel. Enter "=MIN" into a cell to get the minimal value. Start by double-clicking or by typing a "(" where "MIN" is listed in the drop-down menu. Type the first data point's row number and column letter within the parenthesis. To return the smallest value when your data is in a single row or column, insert a colon, the letter, and the number corresponding to the final data point, and then close the parenthesis. Alternatively, you may open the parentheses by selecting the relevant cell, holding down "Shift," and then closing the parentheses by clicking the cell containing the final data point.

If the data is dispersed unevenly, you may define each cell separately by comma-separating them. For example, you might type "=MIN(A2, F3, H12, J1)" to find the minimum values in cells A2, F3, H12, and J1.

By entering "=MAX" in a cell and then following the same steps as for the "MIN" function, you may use the MAX" function to get the highest number from the data set.

How to Calculate Range in Excel

Open your Excel sheet and select the cell where you want to display the range for your data set. For this example, we have our numbers in cells A1 through A5 and use this formula:

=MAX(A1:A5)-MIN(A1:A5)

The first part of the formula MAX(A1:A5)finds the highest value in the data set. The second part MIN(A1:A5)finds the lowest value. The minus sign in between subtracts MIN from MAX, giving us our range: 100 – 10 = 90.

Find a Conditional Range

You may have a situation where you need to find the range of your data set but want to exclude specific data. For instance, you may have provisional sales figures or revenue that you want to eliminate when calculating the range.

You can add a condition to the MAX function using MAXIFS or the MIN function using MINIFS. The syntaxes are MAXIFS(max_range, criteria_range, criteria) and MINIFS(min_range, criteria_range, criteria). Let’s look at an example.
Here we have a data set in cells C1 through C5 where we want to calculate the range but include only the high values, those above 500. We would use this formula:

=MAX(C1:C5)-MINIFS(C1:C5,C1:C5,">500")

The first part of the formula MAX(C1:C5)finds the highest value in the data set. The second part, MINIFS(C1:C5, C1:C5,">500"), finds the lowest value in those same cells but requires the values in those cells to be higher than 500. And, of course, the minus sign is our subtraction to calculate the range.

So rather than determining the range with 5000 – 10 = 4990, the range is calculated as 5000 – 1000 = 4000, excluding those below 500.

Check out Microsoft’s Support pages for these conditional functions if you’re interested in other ways to use MAXIFS or MINIFS.

Finding range in your Excel spreadsheet is as easy as a formula using a couple of functions. And for those who need to take it further, you can add conditions to those formulas.