|
Written By:
Lisa Borga

One of Excel’s prime functions is to allow users to store and analyze data, and in order to do this, we often need to acquire certain metrics, including average, median, and range.

Though all of these metrics are critical, one of the most common is the mathematical range.

This metric tells us the range or the difference between the maximum and minimum values.

Excel provides a few easy-to-use methods to calculate ranges, and learning how to use each of them can make it much easier to work with data.

Here we will discuss what range is in statistics, how it can be used in real-world applications, as well as the functions and methods needed to calculate it for datasets within your workbooks.

## What Is Range

In statistics, the range is simply defined as the difference between the highest and lowest values in a data set.

This can offer a significant indication of the variability in a given set of data. The range can be calculated by subtracting the lowest value from the highest value.

For example, consider a data set of employee salaries in which the highest earner is paid \$500,000, and the lowest earns \$30,000.

In this case, the range would be \$470,000, found by taking \$500,000 and subtracting \$30,000.

The resulting value after subtracting the low and high values is referred to as the range, and a large value indicates a high degree of variability, whereas a low value indicates a low degree of variability.

This can tell you a lot about a data set and is one of the quickest and easiest statistical operations.

## How To Calculate Range Using the Min and Max Functions

When you have a data set stored in Excel, it is easy to calculate the range if the values are stored in ascending order.

In this case, it is simply a matter of subtracting the first value from the last.

However, in practice, most data sets are not already sorted into such a convenient format. Despite this, finding the range, even in such a random data set, is quite simple and convenient.

Excel has built-in functions that can enable you to pull the minimum and maximum values from a dataset. These include the MIN and MAX functions.

• Max Function: The MAX function provides the highest numeric value for a given set of values. The syntax for this function is: =MAX(Starting Value: Ending Value)
• Min Function: The MIN function provides the lowest value in a range of values. The syntax for this function is: =MIN(Starting Value: Ending Value)

The starting and ending values can be numeric values, cell references containing numeric values, or ranges of values. The second value is optional but may be included.

In order to understand how we can use these functions to calculate range, consider a dataset such as this one in which the sales revenue from each salesman is recorded.

If we wanted to calculate the range, we would use the MIN and MAX functions following these steps:

1. Select a blank cell where the range will be displayed. In our case, B12.
2. Input the formula: =MAX(B2:B10)-MIN(B2:B10)
3. Select “Enter” to run the formula

This formula will find the highest and lowest values and give us the difference between them. This is a pretty straightforward procedure.

## Calculating Conditional Ranges in Excel

As you have seen, finding the range is relatively simple, but in the real world, this may not provide very valuable data.

In many cases, data sets include severe outliers or data that contains a significantly different value from the other data points in your set.

These values can significantly skew your calculations and make the range data useless for statistical purposes.

For example, if we had a list of employee salaries in which most earned more than \$30,000, but we had one that earned less than \$5,000, we need a way to discount this data and retain the data that we need to make for a useful range.

This means that we need a method to tell Excel to account for these outliers and ensure that they do not result in flawed data.

Fortunately, Excel provides conditional formulas which can be used to help deal with these outliers.

Consider a data set such as this one in which the range would be significantly distorted by one value that lies significantly outside the others.

While the majority of the values are above \$30,000, one value is only \$3,200, and if we take this on the surface value, it makes it appear that there is a large degree of variability between pay rates for each of these employees, while in truth there is only one outlier.

Fortunately, Excel provides conditional formulas which can be used to account for these abnormalities.

If we wanted to calculate the range for this set of pay data, it is clear that there is one significant outlier that stands out greatly from the rest.

This could be because the employee only began working part way through the year, or maybe they left soon after being hired, but no matter the reason for the difference in pay rate, it may be preferable to exclude this data and only include the more typical pay rates.

In this case, we may choose to tell Excel to remove any values in the data set that have a value of less than 5,000.

There are two ways to do this, and one is to use the MINIFS function. This function was introduced for Excel 2019 and Microsoft 365.

The MINIFS function essentially combines the MIN and IF functions into one function, reducing the size and complexity of the formula you need to use.

The MINIFS function will find the lowest value in the specified range that meets the assigned criteria. In our case, this criterion is any value greater than 5,000.

To do this, we can use this formula to calculate the range while including this condition.

=MAX(B2:B11)-MINIFS(B2:B11,B2:B11,”>5000″)

Just like we used MINIF here to avoid low outliers, we could use the MAXIF function to do the same for outliers with abnormally high values.

In other words, high-value data points could equally skew our range.

The MINIFS formula will analyze the specified data set and only consider any data that is greater than 5,000 while finding the lowest value.

This ensures that outliers with values below 5,000 are ignored, leaving only relevant data for calculating the range for our data set.

Because MINIFS was only introduced for Excel 2019 and Excel 365, it is quite possible you do not have access to this function.

If this is the case, you can always use a combination of the IF function with either or both the MIN and MAX functions to achieve the same result.

For example, to accomplish the same goal in our data, we could use the formula:

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

Once entered, this formula will achieve exactly the same result.

It is simply a bit longer and more cumbersome to use.

## Conclusion

Storing and analyzing data is one of Excel’s primary functions and finding the range in a dataset is one of the most common forms of statistical analysis.

Fortunately, Excel makes it easy to find and calculate ranges for any data set. In this guide, you have seen what range is, how it can be used, and how to calculate range using a variety of easy-to-use functions.

Finally, you have seen how you can calculate range while removing outliers that could skew results.