EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Formula and Functions Excel Function for Range

Excel Function for Range

Madhuri Thakur
Article byMadhuri Thakur

Updated August 9, 2023

Excel Function for Range

Excel Function for Range (Table of Content)
  • Excel Function for Range
  • Examples of Range Function in Excel

Range Function in Excel

Range in Excel is the difference between the maximum and minimum limits of the available numbers in Excel. For example, around 10 different numbers are randomly selected in a list in Excel.

To calculate the Range for these numbers, we need to find the upper and lower values using the MAX and MIN functions in the list of those cells. Once we get the maximum and minimum values out of those numbers, subtract the Max value from the Min value. The returned number will be the range.

There are two kinds of ranges used extensively in Excel, which are illustrated below:

Symmetrical Range: A range that consists of all cells in adjacent positions to each other. Such a range usually shows up as a square or rectangle in a spreadsheet when highlighted. The range shown in the image would be (A1:C4)

Excel function for range 1

Irregular Range: A range consisting of cells that are not contiguous and may not have regular geometrical shapes when highlighted. The range highlighted in the image would be (A1:C4, E1,E4, B6,A7)

Excel function for range 2

Examples of Range Function in Excel

Now, a range would be useless as we have to derive insights from the range’s data. So formulae are used with cell ranges which add the operation we want to perform in the data from the range. For example, if we want to find the sum of the numbers in cells A1 to C4, we would use =SUM(A1:C4)

You can download this Excel Function for Range Template here – Excel Function for Range Template

Excel function for range 3

Example #1 – Finding Maximum and Minimum

1) Finding the maximum and minimum values in a cell range: We use the following functions when looking for minimum and maximum values in a cell range. Please note that this would give us the mathematical result, not the maximum and minimum, as defined by cell number.

  • For Maximum: As illustrated below, we would use the =MAX(Cell Range) function.

Finding Maximum and Minimum example 1.1

  • For Minimum: We would use the =MIN(Cell Range) function as shown below.

Finding Maximum and Minimum example 1.2

2) Suppose we are not interested in the minimum and maximum in the highlighted range but also in the top and bottom k numbers. We can use the following functions to calculate those.

  • For the top k number, say k=3, which means the third-largest number in the range, we would use the function =LARGE(Cell Array,k) for symmetrical ranges or =LARGE((Cell Range),k) for irregular ranges as shown below.

Finding Maximum and Minimum example 1.3

Finding Maximum and Minimum example 1.4

  • A very similar function for finding the kth smallest number in a range would be to use =SMALL(Cell Array, k) for a symmetrical range or =SMALL((Cell Range),k) for an irregular range.

Excel Function for Range- example 1.5

Finding Maximum and Minimum example 1.6

Example #2 – How to Define and Use Ranges in Excel?

We will now look at how to define and use ranges in Excel. First, we need to have data to work with. This can be anything in a spreadsheet ranging from letters to numbers or a combination of both. For the illustrations accompanying this discussion, I am using a sample from a production database that stores data on how many parts are produced in a year.

Excel Function for Range-Example 2.1

Let us say we want to know the range of production numbers entered over the two years. We do this by subtracting the minimum from the maximum value. For this, we need not find each value individually but have to break down the calculation steps and write the formula as follows:

MAX(Cell Range)-MIN(Cell Range)

Please note that the cell range has to be the same in the arguments; otherwise, the formula will not return the correct result.

Excel Function for Range- Example 2.2

We find that the range of products is 368 parts. Now, if we want to find out the occurrence of a particular value in the range, or a range of values within the range, we use another function called COUNTIF. This function has the following syntax:

COUNTIF(Range, value)

Suppose we want to find the month in which we hit more than 300 parts. The formula would be =COUNTIF(C2:C23,”>300″)

Use Ranges in Excel Example 2.3

We find that for 7 months, the production was more than 300 parts. We can also find out if we had any month below a particular number, suppose 100. We would use a nested COUNTIF formula within an IF statement to get a Yes or No answer like this:

=IF(COUNTIF(range,” value”),” Yes”,” No”)

This would look like this:

Excel Function for Range- Example 2.4

The result would be a No as none of the production numbers in the range is below 100. A variation of this can be used to find if we have any production number in a particular value. This would be as follows:

COUNTIF(range,”*”&value&”*”) or COUNTIF(range, value)

The first variation is useful if we want to match two different databases, and the second is when we want to find out if a particular value occurs or not, and if it does, then how many times it reoccurs.

Use Ranges in Excel Example 2.5

Use Ranges in Excel Example 2.6

We can use the MATCH function instead of COUNTIF to find the number of values greater or less than a given value.

Use Ranges in Excel Example 2.7

In the above example, we use the MATCH function to find the number of months with less than 300 parts produced.

Things to Remember

  • We should sort the data in ascending or descending order wherever feasible to simplify operations using ranges.
  • Quotation marks (“”) and asterisks (*) are used in formulae whenever we are looking for substrings or specific text ranges within a range.
  • Irregular ranges are the most common ranges used in business. As such, whenever possible, we should use tables to classify the data before running any operations on them.
  • It must be noted that ranges can be highlighted manually, and Excel displays the number of cells in it as a count at the bottom; however, we can find out the number of rows or columns in a range using the following functions:

ROWS(range)
COLUMNS(range)

Usually, these two functions are not required but are useful for large tables, multiple databases, and recording macros.

Conclusion

Knowledge of range in Excel is an important prerequisite to manipulating data. The range is also used in recording macros and VBA coding; hence, an in-depth understanding of range is necessary for anyone using Excel.

Recommended Articles

This is a guide to Excel Function for Range. Here we have discussed Examples of Range Functions in Excel, along with steps and a downloadable Excel template. You may also look at the following articles to learn more –

  1. VBA Range
  2. VBA Selecting Range
  3. Excel Named Range
  4. Range in Excel
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests
 137+ Hours of HD Videos
36 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Excel Function for Range Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Excel Function for Range Template

EDUCBA

डाउनलोड Excel Function for Range Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more