While sophisticated statistical calculations and analysis are not easy in excel, the software still provides very easy and efficient ways to do statistical analysis.
In a previous blog, we learned to calculate mean, median, and mode in excel. They are the measures of central tendency which is a very important aspect in statistics.
We also learned how to calculate interquartile range in excel which is the difference between the third quartile (75th percentile) and the first quartile (25th percentile).
Today, we are going to see another important statistical measure which is the range.
So, let’s see how to calculate range in excel.
What is Range?
We also learned previously how to calculate interquartile range in excel which is the difference between the third quartile (75th percentile) and the first quartile (25th percentile).
But in general, the range is the area of variation or difference between upper and lower limits on a particular scale.
So, for example, if a data set consists of 15,20,25, and 30. The upper and lower limit would be 30 and 15 respectively and the range would be 15 which means that between 15 and 30, there are 15 data that could possibly occur in any given situation.
Calculating or finding ranges sounds fairly simple. The formula is:
Highest Value – Lowest Value.
But when the data set is very big, it is hard to identify the largest and smallest value.
But excel makes it very easy to calculate or find the range.
So, let’s see how to calculate range in excel.
How to Calculate Range in Excel?
There are quite a few situations where you need to find the range. You can do it in multiple ways in excel. Let’s look at them one by one.
#1 Finding Range in Excel by Sorting the Data
In the picture, there are 10 regions with sales figures from each region.
To find the range, from the Home ribbon go to Sort & Filter and select Sort from Largest to Smallest.
You will get a dialogue box, select Expand the Selection, and press sort.
You will see that the sales figures have been sorted from largest to smallest values. Now to get the range in excel, type the following formula:
=B2-B11
The formula tells excel simply to subtract the smallest value from the largest value.
There you go you have successfully calculated the range in excel.
#2 Finding Range in Excel by MAX and MIN Function
As the range is the difference between the highest and lowest value. We need to tell excel to find the highest or maximum value and the lowest or minimum value
To find the highest value type the following formula:
=MAX(B2:B11)
To find the lowest value type the following formula:
=MIN(B2:B11)
To find the range type the following formula:
=E7-E8
#3 Finding Conditional Range in Excel
Ranges can be misleading, that’s why standard deviation is a much more acceptable measure.
Ranges are misleading because one number can be too big or too small in proportion with the other numbers in the data set.
For example, if we consider our previous data set 15,20,25, 30 and add another number of 3000 you can see that the highest number is blown out of proportions and if we consider that in our range it will be very misleading and won’t project proper analysis.
So, even in this case, if you need to calculate the range, what you can do is take the second-highest number or take the highest number lower than 3000.
In excel you can do it in two ways. Type the following formula to get the conditional range:
=MAXIFS(B2:B11,B2:B11,”<8000″)-MIN(B2:B11)
The MAXIF function is a conditional function like the IF function. The MAXIF function, based on criteria, identifies the largest number. In this case, the criteria were less than 8000, so excel identified the largest number in the data set less than 8000.
You can also use the LARGE and SMALL functions in excel to get or find the range. However, these two functions are not frequently needed. In fact, using them is redundant.
So, there you go, you now know how to calculate range in excel.
Conclusion
There are better statistical measures than range due to its misleading feature. However, the range is one of the foundations before venturing into advanced statistics.
So, calculating and knowing about the range is crucial.
As now you know how to calculate range in excel, you don’t have to sort through big data sets to find the highest and lowest values and waste time.
Hi there, I am Naimuz Saadat. I am an undergrad studying finance and banking. My academic and professional aspects have led me to revere Microsoft Excel. So, I am here to create a community that respects and loves Microsoft Excel. The community will be fun, helpful, and respectful and will nurture individuals into great excel enthusiasts.