Introduction
If you’re working with statistical data in Microsoft Excel, you may need to identify the range for a data set. In this section, we’ll show you how to use formulae to compute a range in Excel.
Range Calculation
Simply explained, range is the difference between the highest and lowest integers in a data collection. If the range is large, the data set is spread out more than if the range is small.
This data collection, for example, has the numbers 10, 25, 50, 75, and 100. To get the range, remove 10 (the lowest number) from 100. (highest number). In this case, the range is 90:
100 - 10 = 90
While this is a basic example, seeing your data set and doing the subtraction is not always as straightforward. You may have a large number of numbers in your data collection, and they may be in a different order than ascending or descending. As a result, locating and subtracting the greatest and lowest numbers may take some time.
To find the greatest and lowest numbers in Excel, utilize the MAX and MIN functions. Then subtract MAX from MIN to get the range: MAX – MIN = Range.
Range Calculation in Excel
Open your Excel sheet and choose the cell where you want the range for your data set to be shown. In this example, we’ll put our numbers in cells A1 through A5 and utilize the following formula:
=MAX(A1:A5)-MIN(A1:A5)
MAX(A1:A5) is the first component of the formula that finds the highest value in the data set. The second section, MIN(A1:A5), determines the lowest value. The negative sign in the middle subtracts MIN from MAX, yielding our range: 100 – 10 = 90.
Locate a Conditional Range
You can be in a position where you need to discover the range of your data collection but wish to omit certain data. For example, maybe you have transitory sales or income statistics that you wish to exclude when determining range.
You may use MAXIFS to add a condition to the MAX function and MINIFS to add a condition to the MIN function. These are the syntaxes:
MAXIFS(max_range, criteria_range, criteria) and MINIFS(min_range, criteria_range, criteria)
Let’s look at an example.
In this case, we have a data set in cells C1 through C5 where we want to compute range but only include the high values, those more than 500. We’d use the following formula:
=MAX(C1:C5)-MINIFS(C1:C5,C1:C5,">500")
MAX(C1:C5) is the first portion of the formula that finds the highest value in the data set. The second component, MINIFS(C1:C5,C1:C5,C1:C5,”>500″), finds the lowest value in the same cells but needs the values to be greater than 500. And, of course, the minus sign represents our subtraction in order to compute the range.
Rather than calculating range as 5000 – 10 = 4990, the range is computed as 5000 – 1000 = 4000, omitting values less than 500.
If you’re looking for alternative methods to utilize MAXIFS or MINIFS, see Microsoft’s Support pages for these conditional functions.
Finding a range in an Excel spreadsheet is as simple as utilizing a formula and a handful of functions. You may even add conditions to those formulae if you want to take it a step further.
