Excel Function for Range (Table of Content)
Range Function in Excel
In mathematics, a range is defined as the values between a defined minimum and maximum. Excel uses this concept to define ranges in a spreadsheet. We use ranges in many of our day to day work, such as for calculating formulae, for making tables and so on. The ranges in excel are usually defined from the upper left cell as the minimum and the lower-left cell as the maximum. There are two kinds of ranges used extensively in excel which are illustrated below:
Symmetrical Range: A range which 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)
Irregular Range: A range which consists of cells which 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)
Examples on Range Function in Excel
Now a range in itself would not be useful as we have to derive insights from the data that the range provides. 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 till C4, .we would use =SUM(A1:C4)
Example #1 – Finding Maximum and Minimum
1) Finding the maximum and minimum values in a cell range: We use the following functions when we are looking for minimum and maximum values in a cell range. Please note that this would give us the mathematical result and not the maximum and minimum as defined by cell number.
- For Maximum: We would use =MAX(Cell Range) function as illustrated below.
- For Minimum: We would use =MIN(Cell Range) function as shown below.
2) Suppose we are not interested just in the minimum and maximum in the highlighted range, but also in the top and bottom k numbers in the range. We can use the following functions to calculate those.
- For 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.
- 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.
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 which stores data on how many parts are produced in a year.
4.9 (3,049 ratings)
View Course
Let us say we want to know the range of production numbers that have been 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 breakdown 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 would not return the correct result.
We find that the range of production is 368 parts. Now, if we want to find out the occurrence of 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)
Let us suppose we want to find what was the month in which we hit more than 300 parts. The formula would be =COUNTIF(C2:C23,”>300″)
We find that for 7 months, the production was more than 300 parts. We can also find out if we had any month which was 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:
The result would be a No as none of the production numbers in the range are 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 when we want to find out if a particular value occurs or not, and if it does, then how many times it reoccurs.
We can use the MATCH function instead of COUNTIF in case we want to find the number of values greater or less than a given value.
In the above example, we use the MATCH function to find the number of months that had less than 300 parts produced.
Things to Remember
- When using ranges, we should sort the data in ascending or descending order wherever feasible to simplify operations.
- Quotation marks (“”) and asterisk(*) 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 and multiple databases, as well as for recording macros.
Conclusion
Knowledge of range in excel is an important pre-requisite to being able to manipulate data. Range are also used in recording macros and VBA coding and hence an in-depth understanding of range is a must for anyone using excel.
Recommended Articles
This is a guide to Excel Function for Range. Here we have discussed Examples of Range Function in Excel along with steps and downloadable excel template. You may also look at the following articles to learn more –
