Excel Range Formula

In Excel, “range” has two meanings. One is the collection of cells in the dataset, the Excel range of cells, which is the more widely used reference. The other is the statistical reference of range as a mathematical function used to determine the range of a particular dataset. This is the range we are going to discuss today

Usually, a simple Excel RANGE formula does the job when it comes to calculating range in a dataset. However, the formula gets complicated at times with reference to how to use them and where. I’ll share a few examples to help you out with this aspect as well.

However, if you’re looking for a comprehensive guide to all your Excel queries, upGrad brings to you the Excel Free Online Course with Certification in 2024. Grab this opportunity just a click away!

What is Range?

In statistics, range defines the distribution of values or simply put, the difference between the highest and the lowest values in a set of given data. As with almost every other mathematical function, Excel has a way to determine this range as well. For example, in a given set, I enter the highest value entry as 100 and the lowest as 20. So the range in this case would be 100 - 20 = 80, it’s simple subtraction.

Several mathematical functions such as MAX, MIN, SMALL, LARGE, and so on can be used to determine the range in a selection of cells.

How to Calculate Range in Excel?

There are several functions to help you calculate range in Excel. First, you have to perform the Excel range selection. The selection can be symmetric or irregular, as per your requirement.

Now point out the minimum cell reference and the maximum cell reference. Once you have figured this out incorporating it into the selected Excel RANGE function will give you the range.

Excel Functions to Calculate Range

The following functions and their use need to be understood to calculate Excel or VBA range.

Suppose, a given dataset starts from B2 and ends at B30. In all these 29 inputs we have the class score during a test.

• MAX: This function returns the maximum or highest value from the set.
  For example: Implementing this formula on the above dataset will return 99
  Formula: =MAX(B2:B30)
  

• MIN: The MIN function is used to return the minimum value from given dataset.
  For example: Running this function on the above dataset will give us 3
  Formula: =MIN(B2:B30)
  

• SMALL: This function returns the x-th smallest value from the VBA range cells, where "x" is the limit specified by the user.
  Such as: Calculate the 2nd largest value in a dataset of class scores Formula: =SMALL(B2:B30, 2)
  Answer: 5
  

• LARGE: Similarly, The LARGE function retrieves the y-th largest value from a dataset, where "y" is a user-defined parameter.
  Such as: Find the 3rd largest value from the set of class scores
  Formula: =LARGE(B2:B30, 3)
  Answer: 98 (because two students scored 99)
  

• MINIFS: MINIFS is used to find the minimum value in a range based on specified criteria. It allows you to apply conditions or criteria to filter the dataset before calculating the minimum value.

For example: You want to ignore students who scored below 20

Formula: =MINIFS(B2:B30, B2:B30,">20")

• MAXIFS: Similar to MINIFS, MAXIFS is the Excel RANGE function that calculates the maximum value in a range based on specified conditions or criteria applied to the dataset.

For example: You want to ignore all students who scored above 80

Formula: =MAXIFS(B2:B30, B2:B30,"<80")

• COUNTA: COUNTA function in Excel will give you the number of non-empty cells in a given range, even including error returns in certain cells, if any.
  

• COUNTIF: COUNTIF or Excel RANGE if formula, counts the number of cells within a range that meet a specific condition or criteria.

Problem statement: Count the number of students who scored more than 80 in the test

For example: =COUNTIF(B2:B30, ">80")

Answer: 8

• IF: The IF function in Excel ranges performs a logical test and returns one value if the test is true, and another value if the test is false.

Such as: Implement a pass/fail condition in the given dataset such that if a student scores more than 30 he passes or he fails.

Formula: =IF(B2 >= 70, "Pass", "Fail")

Excel Range Example

Here are some real-world applications of calculating range in Excel:

1. Temperature variation analysis
A weather station recorded temperatures of a particular region for a month. Calculate the range (minimum temperature recorded is 20°F and maximum is 50°F) to understand the variability in daily temperatures.

2. Product price analysis
Suppose, an e-commerce platform tracks product prices over a year and now you can determine the price range within specific categories. The data you have to analyze has a minimum value of $50 and a maximum of $150 to mark pricing trends with the help of Excel RANGE lookup.

3. Stock market assessment
An investor evaluates stock prices over a quarter. Determine the spread between the highest and lowest stock prices (considering the lowest stock price as $100 and the highest as $120) to assess market volatility.

4. Athlete performance records
A sports coach monitors athlete sprint times. Calculate the range of performance data to assess improvements or variations, if the fastest athlete recorded a sprint time of 10 seconds and the slowest of 15 seconds..

5. Vehicle efficiency comparison
A car manufacturer tests fuel efficiency across models to find that the lowest fuel consumption rate is 20 MPG and the highest is 40 MPG. Calculate the range of fuel consumption rates to understand variability among vehicles.

6. Customer satisfaction measurement
A service company collects customer ratings on a scale of 1 to 10 with 5 being the lowest recorded rating and 9 being the highest and inputs the data with Excel fill. Determine the range of satisfaction scores, evaluate service quality, and identify areas for improvement.

Wrapping up

Calculating range with the help of Excel RANGE formula gets easier with practice. Now you might ask yourself, what is the benefit of calculating range in Excel? Well, Excel, with practice, will yield you results faster than manual calculations and thus increase your efficiency.

In case you are looking to build on your Excel skills as a whole and end up in the good books of your boss, consider practicing these formulas with varied functions every day. If you are eager to master all the functions in MS Excel, quickly head over to upGrad’s Excel certification course and upskill yourself today!

FAQs

1. How to calculate the range?

Calculating the range of a given dataset is simpler than it sounds. Simply subtract the lowest value in the given set from the highest, leaving you with the range.

2. What is data range in Excel?

The data range in Excel is a group of cells that have been selected on the worksheet. After you select your data range, you can carry forward any function to manipulate or extract the data.

3. What is the range format in Excel?

To specify a certain range in Excel while performing a function, simply write the range as =FUNCTION(Lowest cell range: Highest cell range).

4. What is column range?

Column range is the span of columns in a spreadsheet that indicates the total number of columns present within the selection or the sheet as a whole. It defines the width or extent of data across multiple columns.

5. What is cell range with example?

A cell range refers to a group of adjacent cells in a spreadsheet, identified by the starting cell and ending cell and separated by a colon (:). For example, "A1:B7" represents cells from A1 to B7, inclusive of rows and columns.