If you’re using Microsoft Excel for statistical data, you may need to find the range for a data set. Here we’ll explain a simple way to calculate range in Excel using formulas.
Simply put, the difference between the highest and lowest numbers in a data set is known as range. If the range is high, then the data set is spread out further than if the range is low.
As an example, this data set includes the numbers 10, 25, 50, 75, 100. To find the range, you subtract 10 (lowest number) from 100 (highest number). Here, the range is 90:
100 - 10 = 90
While this is a simple example, it’s not always that easy to view your data set and do the subtraction. You may have many numbers in your data set, and they may be in varying order rather than ascending or descending. So, spotting and subtracting the highest and lowest values might not be a quick process.
In Excel, you can use the MAX and MIN functions to find the highest and lowest values. Then, do the subtraction: MAX – MIN = Range.
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:
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.
You may have a situation where you need to find the range of your data set but want to exclude certain data. For instance, maybe you have temporary figures for sales or revenue that you want to eliminate when calculating 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 range but include only the high values, those above 500. We would use this formula:
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 be higher than 500. And of course, the minus sign is our subtraction to calculate the range.
So rather than determining range with 5000 – 10 = 4990, the range is calculated as 5000 – 1000 = 4000, excluding those numbers below 500.
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.
For additional help, take a look at some basic functions you should know about in Excel.
- › How to Find the Smallest or Largest Number in Microsoft Excel
- › Work and Play Anywhere With the ROG G14 Laptop, Now $400 Off
- › AMD’s Ryzen 7000X3D Pricing Looks Really, Really Good
- › Intel’s Arc A750 Graphics Card Has a New Lower Price
- › Samsung Made a Music Video for the Galaxy S23’s Ringtone
- › Windows 10’s Bad Upgrade Prompts Are Back for Windows 11
- › Windows 11 Is Adding Even More Widgets