Usually, we refer Range in Excel is a cell or collection of cells in worksheet.
But in this tutorial, we discuss about Range which refers the range in data set i.e. the difference between the highest and lowest value in data set.
In a simple data set which includes only 110, 130, 135, 140, 150 and 160. To determine the range, we fist find out the MIN value i.e., 110 and MAX value i.e., 160.
So, the Rage is 160 – 110 = 50.
Let’s take a little bit large data set to find range on excel. The fist set of data is Sorted, so we can find easily the Minimum value is 13,320 and Maximum value is 43,125, accordingly Range is 43,125 – 13,320 = 29,805.
But in reality, data will not be sorted as mentioned in first data set, in second data set there are unsorted data. Let’s see how to calculate Rage by using MIN and MAX functions.
In the second data set range is calculated through MIN and MAX function. Formula for Range in Excel:
Range = MAX(B2:B22) – MIN(B2:B22)
How to calculate Range in Excel by using Outlier functions?
Sometimes we want to determine range of data by excluding certain unusual, temporary, or seasonal data. Due to certain factors data in a particular period data may be substantially higher or lower than usual data this unusual data is called outlier. In that case we need to eliminate these data.
We can incorporate a condition to MAX function using MAXIFS or MIN function using MINIFS. Both MAXIFS and MINIFS functions are available in later versions of Excel 2019 and Office 365 subscribers.
First, we use MINIFS function in the below data set. The syntax for the MINIFS function is MINIFS(min_range, criteria_range1, criteria).
Here we excluded data those are below of 5,000. Due to certain factors two years data are substantially lower than other years’ data. So we consider data those are above of 5,000.
For Excel 2016 or earlier version, we can incorporate IF function in MIN function to exclude outlier data from a data set. See the below formula:
Second, we use MAXIFS function in the below data set. The syntax for the MAXIFS function is MAXIFS(max_range, criteria_range1, criteria).
Here we excluded data those are above of 75,000. Due to certain factors two years data are substantially higher than other years’ data. So, we consider data those are less than of 75,000.
For Excel 2016 or earlier version, we can incorporate IF function in MAX function to exclude outlier data from a data set. See the below formula:
You may be interested:
