We usually require certain metrics to understand the data that we are working with. There are a number of such representative metrics, like the average, the median, etc. Among these metrics, an often-used value is the ‘Range’.
In this tutorial, we will show you two easy ways in which you can find the range of a series of numbers in Excel:
- Using a formula with the MIN and MAX built-in functions
- Using a formula with the SMALL and LARGE built-in functions
What is Range and How is it Calculated?
The range is a measure of the spread of values in a series. In other words, it is the variation between the upper and lower limits of the series on a particular scale.
To find the range of a set of numbers, you need to find the difference between the largest and smallest numbers.
For example, if you have a series of numbers {4,2,6,5,3} then the range can be calculated as follows:
Range = largest value - smallest value
= 6 – 2
How to Find the Range in Excel
Calculation of the range is a very simple process, requiring three basic arithmetic operations:
- Finding the largest value
- Finding the smallest value
- Finding the difference between the two
Given below are two methods to quickly calculate the range of a set of numbers in Excel. To demonstrate both methods, we will use the following dataset:
Finding the Range in Excel with MIN and MAX Functions
The first way to find the range is to use a combination of the MIN and MAX functions.
The MIN Function
The Excel MIN function returns the smallest numeric value in a range of values. The syntax for the MIN function is as follows:
=MIN (number1, [number2], ...)
Here,
- number1 can be a numeric value, a reference to a numeric value, or a range of numeric values.
- number2,… is optional. It can be a numeric value, a reference to a numeric value, or a range of numeric values.
For example, to find the minimum value of numbers in the range B2:B7, you will write the MIN function as follows:
=MIN(B2:B7)
The MAX Function
The Excel MAX function returns the largest numeric value in a range of values. The syntax for the MAX function is as follows:
=MAX (number1, [number2], ...)
Here,
- number1 can be a numeric value, a reference to a numeric value, or a range of numeric values.
- number2, … is optional. It can be a numeric value, a reference to a numeric value, or a range of numeric values.
For example, to find the maximum value of numbers in the range B2:B7, you will write the MAX function as follows:
=MAX(B2:B7)
Note: Both MIN and MAX functions ignore empty cells, logical values like TRUE and FALSE, as well as text values.
Also read: How to Find the Largest Value in Excel
Using the MIN and MAX functions to Find the Range of A Series
To find the range of values in the given dataset, we can use the MIN and MAX functions as follows:
- Select the cell where you want to display the range (B8 in our example).
- Type in the formula: =MAX(B2:B7)-MIN(B2:B7)
- Press the Return key.
Note: You can replace the reference B2:B7 with reference to the cells containing the values you want to calculate the range for.
Explanation of the Formula
The formula simply performed the basic steps required to calculate the range:
- Finding the largest value: =MAX(B2:B7)
- Finding the smallest value: =MIN(B2:B7)
- Finding the difference between the two: =MAX(B2:B7) – MIN(B2:B7)
Finding the Range in Excel with SMALL and LARGE Functions
The second way to find the range is to use a combination of the SMALL and LARGE function.
The SMALL Function
The Excel SMALL function returns the ‘n-th smallest value’ in a range of values. So you can use it to find the 1st smallest value, 2nd smallest value, 3rd smallest value, and so on.
The syntax for the SMALL function is as follows:
=SMALL (array, n)
Here,
- array is the range of cells that you want to find the n-th smallest value from.
- n is an integer that specifies the position from the smallest value, i.e. the nth position.
For example, to find the 3rd smallest value in the range B2:B7, you will write the SMALL function as follows:
=SMALL(B2:B7,3)
Similarly, to find the smallest value in the range B2:B7, you will write the function as follows:
=SMALL(B2:B7, 1)
Notice the above function gives a result equivalent to the function:
=MIN(A2:A7)
The LARGE Function
The Excel LARGE function returns the ‘n-th largest value’ in a range of values. So you can use it to find the 1st largest value, 2nd largest value, 3rd largest value and so on.
The syntax for the LARGE function is as follows:
= LARGE (array, n)
Here,
- array is the range of cells that you want to find the n-th largest value from.
- n is an integer that specifies the position from the largest value, i.e. the nth position.
For example, to find the 3rd largest value in the range B2:B7, you will write the LARGE function as follows:
= LARGE (B2:B7,3)
Similarly, to find the largest value in the range B2:B7, you will write the function as follows:
= LARGE (B2:B7, 1)
Notice the above function gives a result equivalent to the function:
=MAX(B2:B7)
Note: In cases where you’re working with large volumes of data, using the MIN and MAX functions are more efficient to use than the SMALL and LARGE. This is because the SMALL and LARGE functions require more computing and resources.
Using the SMALL and LARGE functions to Find the Range of A Series
To find the range of values in the given dataset, we can use the SMALL and LARGE functions as follows:
- Select the cell where you want to display the range (B8 in our example).
- Type in the formula: =LARGE(B2:B7,1) – SMALL(B2:B7,1)
- Press the Return key.
Note: You can replace the reference B2:B7 with reference to the cells containing the values you want to calculate the range for.
Explanation of the Formula
The formula simply performed the basic steps required to calculate the range:
- Finding the largest value: = LARGE(B2:B7,1)
- Finding the smallest value: = SMALL(B2:B7,1)
- Finding the difference between the two: =LARGE(B2:B7,1)-SMALL(B2:B7,1)
Applications and Limitations of the Range
The range provides a great way for us to get a basic understanding of how spread out the numbers in the dataset are.
So, a higher range value means the data is quite spread out, while a smaller range value means the data is less spread out, or more concentrated.
It must be noted, though, that the range is a very crude measurement since it is quite sensitive to outliers.
A single value that is too high or too low can completely alter the range, giving an erroneous representation of the data. As such, it doesn’t always provide a true indication of the spread in the dataset.
Having said that, the range is easy to calculate. It only requires basic operations. So, it is a good way to help you get a very basic understanding of the nature of your data.
Other Excel tutorials you may also like:
