Excel users often tend to use the phrase “Range” which refers to a cell or collection of cells. However, it clearly has a different meaning than “Range” that we are going to discuss here. Today, we will talk about Range used in the mathematical field that simply refers to the range in the dataset, such as the range between the minimum and maximum value given in a dataset.
How to calculate range in Excel is the most common thing you might be wondering to find an answer. For the common statistical analysis, Excel uses straightforward tricks to calculate range. Let’s find out how you can find the range in Excel.
What is Range?
Suppose you have a dataset of student scores and the range would be the spread of values in that data. In this dataset, the minimum score is 15 and the maximum score is 98, so the spread would be 73.
Range = 98 – 15
Range simply refers to the difference between the maximum and the minimum value of the given dataset.
Excel Functions Used for Range
Excel lets you use different functions to make all kinds of calculations depending on the data available in the cells. Suppose, you have a dataset of numeric in cells A1 to A10. You can easily find out the highest number in the cells. Put the formula to use a function in a blank cell.
Below are some useful functions used in Excel related to the range:
MAX: Using this function, you can return the maximum value of a range. It will be as =MAX(A1:A10).
MIN: Using this function, you can return the minimum value of a range. It will be as =MIN(A1:A10).
COUNTA: Using this function, you can return the number of values given in a range that contains text and numbers. It will be as =COUNTA(A1:A10).
COUNTIF: Using this function will return YES if a value is available in the given range. In case, when the value isn’t in the range, it returns NO. It will be as =COUNTIF(A1:A10).
SMALL: Using this function can return the smallest value in a given range and you can write it as =SMALL(A1:A10).
LARGE: Using this function can return the largest value in a given range and you can write it as =LARGE(A1:A10).
RANK: Using this function can help you rank all values in a given range. Usually, the rank is the position of values given in the range, and one value is the highest rank. It will be as =RANK(A1:A10).
MEDIAN: Using this function can return the median value of the given dataset in an array. It will be as =MEDIAN(A10, 11, 12).
IF: Using this function can return a value as per the condition. It will be as =IF(A1:A10, “low”, “high”).
How to Calculate Range in Excel
When the given dataset has sorted values, the main thing you need to do is subtract the primary value from the ending value. On the other hand, when your dataset does not have sorted values, you can find the range as well. With Excel, you can calculate a range using the maximum and the minimum value from a given range.
For instance, in a dataset, you need to find the range for the data in column B.
Use the following formula to calculate the range:
=MAX(B2:B11)-MIN(B2:B11)
Using the above formula lets you find the maximum and the minimum value. Isn’t it simple?
How to Calculate Range in Excel with the SMALL and LARGE Functions
Below are some easy-to-follow steps to find the range in Excel with the help of SMALL and LARGE functions:
- Add data in cells A1 to A10.
- Put the formula =SMALL(A1:A10) in cell B1.
- Now, add the formula in cell B2 =LARGE(A1:A10).
- Subtract the two formulas by adding =B2-B1 in cell C1.
- Now, you got the range of data.
How to Calculate Range in Excel with the Conditional MIN and MAX Formulas
Sometimes, you may have to find the range for a dataset, which is compatible with specific conditions. Below we have some steps used for finding the range in Excel with the help of conditional MIN and MAX formulas.
- Add data in cells A1 to A10.
- Put the formula in cell B1 =IF(A1:A10>5, “MAX”).
- Add formula in cell B2 =MIN(A1:A10).
- Now, enter =B2-B1 in cell C1 to subtract the two formulas.
- You got the range of data now.
Final Thoughts
You are now familiar with how to find the range in Excel by using multiple functions available in Excel. Practice makes a man perfect that’s why you have to keep on trying the above-mentioned methods. Explore Excel for more functions to make your data organized.
