Statistically, Range is the difference between the minimum and maximum value of a data set. Most analysts like to calculate ranges to check the distribution of your values in your data set.
In Excel, there isn’t a specific function that calculates range. You can, however, use other functions and create a formula to calculate this value.
How to Calculate Range
To calculate the range, you will need the lowest and the highest value of a data set. Range is the difference between the minimum value and the maximum value of an array. Mathematically,
Range= Maxmimum Value - Minimum ValueWe will be using this very concept to calculate range in Excel.
How to Calculate Range with No Criteria in Excel
You can calculate a normal range using the MIN and MAX functions in Excel.
Step 1: Calculate the Minimum Value using MIN
The MIN function in Excel takes a range as a reference and returns the lowest number. You can use the MIN function to first locate the lowest value you will be using to subtract from the highest value. Here is how MIN is used in a formula:
=MIN(reference)
Step 2: Calculate the Maximum Values using MAX
You can identify the largest value that exists in a range using the MAX function. The MAX function in Excel is similar to the MIN function in syntax, only returning the maximum value instead of the minimum. Here’s how you can construct a formula using the MAX function:
=MAX(reference)
Step 3: Calculate the Range
As we stated before, the range is the difference between the minimum and the maximum value of a range. We will be using the two values we generated using the MIN and MAX functions and subtracting the minimum value from the maximum value.
In our spreadsheet, cell D5 contains the minimum value while cell D6 contains the maximum value. Using this information, here’s how we can calculate the range value:
=D6-D5
How to Calculate Range with Criteria in Excel
There can be situations where you might have to exclude certain minimum and maximum values while calculating the range. There are two distinct functions, including the MINIFS function and MAXIFS function to set criteria to calculate range in Excel.
Set Criteria for the Minimum Value
The MINIFS function is responsible for setting the criteria to calculate the lowest value in a range. You can set as many criteria as you require in the MINIFS function. Only the ones you mention as your criteria inside the MINIFS function will be taken into account while calculating the minimum value of a range.
Here’s how the MINIFS function is written when creating a formula:
=MINIFS(min_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)Example Using the MINIFS function
In this data range, I have the marks scored by 10 students in a class. The ones that were absent during the test are marked as “ab”, and the ones present are marked as “p” in the corresponding cell.
The MIN function in this case will not be useful as it will take into account the students that did not appear on the test. To exclude them, we will be using the MINIFS function.
Here’s the formula we created using the MINIFS function:
=MINIFS(B2:B11,C2:C11,"p")
After calculating the minimum, we calculated the maximum value using the MAX function. Then, we subtracted the maximum value from the minimum to get the range.
Set Criteria for the Maximum Value
You can also set one or as many criteria to calculate the maximum value of an array in Excel using the MAXIFS function. Similar to the MINIFS function, MAXIFS will only refer to the numbers you passed as your criteria when computing the maximum value.
Here’s the syntax used when writing the MAXIFS function:
=MAXIFS(max_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)Example Using the MAXIFS function
Let’s see the MAXIFS function in action. In this data table, we again have marks scored by 10 students in a class. However, some of them are reappearing for this exam. In column C, the ones who are appearing on the exams for the first time are marked as “regular”, and the ones that are reappearing for the exams are labeled as “back”.
The class teacher wants to see the maximum and minimum scores obtained by only those students who are appearing on this test for the first time.
This is how we will be creating our formula using the MAXIFS function:
=MAXIFS(B2:B11,C2:C11,"regular")
As the teacher wants to use calculate the range of students appearing the test for the first time, we will again be using the MINIFS function and set the criteria as “regular” for range C2:C11. Then, we will subtract cell F6 from F5 to calculate the range.
